Additional help in extracting data from string of characters

[easybpw]

Hello again,

Earlier I asked for some help in extracting the last name of the employee in the string of characters listed below. The help was excellent and the formula below works perfectly. Now I've run up against another issue with this string. I now need to extract the 3 numbers to the right of the last : . In this example I am looking for "005". The formatting will always be the same with the same spaces between. I tried to adapt the formula below but it's not working. Thanks in advance for your assistance.

Tender type: Staff Charge Employee ID: 99999999 Cost centre: 005 Smith,John



=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(",",A1),LEN(A1),"")," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[CalcSux78]

try:

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," "," ")," ",REPT(" ",100)),200),100))

 

[Eric W]

Getting trickier!

Try:

=TRIM(LEFT(RIGHT(SUBSTITUTE(REPLACE(A1,FIND(",",A1),LEN(A1),"")," ",REPT(" ",LEN(A1))),2*LEN(A1)),LEN(A1)))

I like this one a bit more than the one by CalcSux78 because if you have a name like "Smith,Mary Ann", it handles it better. But either way, test it on a lot of examples. Parsing data like this is notoriously hard.

 

[Rick Rothstein]

Assuming the text will not be longer than 300 characters (increase the 300 if it will), this should work...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",300)),300)),3)

 

[CalcSux78]

Good catch, Eric! I actually used one of your older posts as inspiration.

 

[Eric W]

And no doubt I got the inspiration for that post from someone else who came before!

And then Rick comes along, looks at the problem from a different angle, and comes up with the shortest version yet.

 

[kweaver]

These are always fun because there are a number of ways to find those 3-digits.
(in my example, the original string is in E4)

If the spacing is always the same as stated, then the digits are after the 9th space. I think this will find the three digits also.

=MID(E4,1+FIND(CHAR(160),SUBSTITUTE(E4," ",CHAR(160),9)),3)

If you look specifically for the last colon, then I believe this will work (assuming, again, there's a space before the 3-digits):

=LEFT(RIGHT(E4,LEN(E4)-FIND(CHAR(160),SUBSTITUTE(E4,":",CHAR(160),LEN(E4)-LEN(SUBSTITUTE(E4,":",""))),1)-1),3)

You could also look for "centre:" if that's always there (assuming that 005 is the cost center?)

=MID(E4,8+FIND("centre:",E4),3)