Can i separate the last numbers into another column

[Neha83]

Ive tried text to column in every way possible but as some are 2 digit and some are 3 digit . It doesnt seem possible. Please let me know if you know what i can look at to separate the digits - any tips would be great.

 

[JEC]

=TEXTAFTER(A1:A7," ",-1)

or for earlier excel versions

=MID(A1,SEARCH(" ?A ",A1)+4,9)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Neha83]

=TEXTAFTER(A1:A7," ",-1)

or for earlier excel versions

=MID(A1,SEARCH(" ?A ",A1)+4,9)

Thank you use much - it works great

 

[Arts]

Hi

May I ask what the +4 is doing in the formula =MID(A1,SEARCH(" ?A ",A1)+4,9).

I see that it is searching for "A" which is at the end of the string but can't quite see why the +4 is required.

Many thanks

 

[JEC]

The search function tries to match a space, followed by anything (?), then the letter A and then a space again.
It will return the position of that pattern. The pattern starts at the first space so you have to add +4 positions (length of the pattern) to get to the start of the number at the end.

 

[Arts]

Got it, thank you! (I thought it was looking for the "A" at the end not how you described)