Share via

Removing letters only from a cell

Anonymous
2023-06-30T13:56:08+00:00

hi all

I have a report and one of my columns contains cells each with two letters and then a different amount of numbers. For example, A1 = AP454, A2 = ST344444, A3 = RT288. So every cell begins with two letters but they are followed by a different amount of numbers each time.

Is there a formula I can use that will remove the first two characters of the cells, ie. just the two letters and leave me with all the remaining numbers? I've tried RIGHT but I need it to get a different amount of digits from the right on each cell, so it won't work.

Any ideas??

TIA, Louise

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-06-30T14:06:33+00:00

    Dear respected Louise Wooding,

    Good day! Thank you for posting to Microsoft Community. We are happy to help you.

    As per your description, you may use below formula:

    =--MID(A1,LEN(LEFT(A1,2))+1,LEN(A1))

    If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information for better understanding and guiding you further.

    Appreciate your patience and understanding and thank you for your time and cooperation.

    Sincerely,

    Waqas Muhammad

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-07-03T12:55:49+00:00

    Hi respected Louise Wooding,

    I'm writing a follow up this case, and I haven’t received any information from you, may I know have you checked above reply? Feel free to post back if you need further assistance.

    Have a good day and stay safe 😊

    Sincerely,

    Waqas Muhammad

    0 comments
  3. Anonymous
    2023-06-30T21:53:37+00:00

    RIGHT does work as follows:

    =--RIGHT(E5,LEN(E5)-2)

    Here is an even shorter way, that any of these:

    =--REPLACE(A1,1,2,)

    Note you can drop the leading -- if returning the numbers as text is ok

    =REPLACE(A1,1,2,)

    The -- is called a unary minus and it serves here to convert "text" numbers into their value.

    I should add the

    =--MID(A1,3,9)

    Also works if the number portion is 9 or less. If not you can replace the 9 with 99.

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2023-06-30T13:57:27+00:00

    Think I've just worked it out - could I use MID?

    0 comments