# Thread: Need formula to extract from string

1. ## Need formula to extract from string

Need some help

How can I create a formula that will extract the last 4 characters ( in this case numbers) of a string which may also contain a number of spaces before the last 4 characters.

2. Alrom,

Try this: =Right({your string or reference here},4)
ExcelRight.JPG

3. The Geek's formula is correct. If by chance there are spaces within the last 4 characters but you want to exclude them then add the trim command:
This will remove all spaces from the text prior to evaluating it with the "Right" command

HTH

4. Maudibe,

Sorry trim only removes spaces at the beginning & ending of a string.
It would however strip spaces from the trailing end.

5. Maybe get rid of all the spaces before selecting the right 4: =right(substitute(A1," ",""),4)

6. RGeek,
Correct u r ....thx

7. Thanks for your help so far. I have been playing around with your suggestion but still have issues.

Here is an example of the type of string I am working with "John P. Doe 1945" in cell A1. I want to end up with only "1945" as a number in cell A3 and "John P. Doe" in cell A2. If someone adds a space at the end of the string, part of the number "1" , "19" if two spaces, appear in A2 and missing from A3. Help.

8. in A2:
=MID(TRIM(A1),1+FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),LEN(A1))

in A3:
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

Steve

9. Alrom,

Ok, I think this is what you want.
In Col B: =TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-4)))
In Col C: =RIGHT(TRIM(A2),4)
RIghtTrim.JPG

10. Hi alrom

Another idea maybe of some help or not!

Based on RetiredGeek sheet.

In C2: =--RIGHT(SUBSTITUTE(A2," ",""),4)

in B2: =TRIM(SUBSTITUTE(A2,C2,""))

11. I would like to attach a thumbnail pic of my problem but if I do a screen print and save it as a jpeg file and attach it turns out so small it is impossible to see. RGeek, how do you create your thumbnail?

If you can read this attachment, I need a space in E4 between Doe and 67

12. Alrom,

Your attachment is entirely readable if you just click on it to enlarge it.
I usually use the Snipping Tool in Win 7/8 and save the file as a .jpg and then attach it.

Without the error check the formula would be =CONCATENATE(B2," ",D2)
You don't need 2 concatenate functions since you can have many arguments to one.

BTW: concatenate will NOT generate an error if one or all of the arguments returns a blank it will just return a blank.You may want something along this line if I read your code example correctly:
=IF(ISBLANK(D3),CONCATENATE(B3," ",C3),CONCATENATE(B3," ",D3))
concatenate.JPG

13. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

alrom (2012-11-29)

14. Thanks RG, and I thought I knew everything about win7. BTW if D3 is a blank then I will except it. the idea is the return the persons age based on the year in C3, if the year is missing, so be it.

Thanks again.

15. Hi alrom

Another option could be!

Names in cells A3:A5 and the date 2012 in B1 (Current year)
In B3 and copy down:

=TRIM(LEFT(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&12 34567890))-1))&" "&\$B\$1-RIGHT(A3,4)

Or if you still require as your picture:

In B3: =TRIM(SUBSTITUTE(A3,C3,""))
In C3: =--RIGHT(SUBSTITUTE(A3," ",""),4) Note include the double unary.
In D3: =\$B\$1-C3
In E3: =B3&" "&D3

Kevin

16. Retired Geek, Is there any advantage to using the Concatenate function over something like =A2 & " " & A3?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•