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.
Printable View
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.
Alrom,
Try this: =Right({your string or reference here},4)
Attachment 33393
:cheers:
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:
=Right(trim({your string or reference here}),4)
This will remove all spaces from the text prior to evaluating it with the "Right" command
HTH
Maudibe,
Sorry trim only removes spaces at the beginning & ending of a string.
It would however strip spaces from the trailing end.
:cheers:
Maybe get rid of all the spaces before selecting the right 4: =right(substitute(A1," ",""),4)
RGeek,
Correct u r ....thx
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.
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
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)
Attachment 33416
:cheers:
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,""))
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
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))
Attachment 33432:cheers:
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.
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
Retired Geek, Is there any advantage to using the Concatenate function over something like =A2 & " " & A3?