Find First Letter in second word and add to formula

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
I could use some assistance this morning please.
I need to locate and extract the first letter of the second word and add it to an existing formula.

The following is the existing formula. I need to add column "I" a second time, immediately following the first occurrence of "I" but it needs to extract the first letter of the second word which is proceded by a space.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F146&LEFT(I146,1)&(LEFT(P146,1)&LEFT(J146,1)&LEFT(M146,2)&LEFT(K146,1)&LEFT(R146,1)&LEFT(O146,1)&LEFT(N146,2)&LEFT(Q146,2)&RIGHT(U146,2)),".",""),"/","")," ","")

Example:
Existing Text Phrase:
CAMO FORREST
Extract the "F"

Thanks,

Doug
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
This will extract the "F" from your phrase:

=MID(A2,FIND(" ",A2)+1,1) where A2 houses the string...

not sure though, how you want to add it to the formula you already have....

without seeing sample data and what is already be extracted it's hard to say.
 
Upvote 0
Extra big thanks you guys!
This did it right off: here is the final version.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F146&LEFT(I146,1)&MID(I146,FIND(" ",I146)+1,1)&(LEFT(P146,1)&LEFT(J146,1)&LEFT(M146,2)&LEFT(K146,1)&LEFT(R146,1)&LEFT(O146,1)&LEFT(N146,2)&LEFT(Q146,2)&RIGHT(U146,2)),".",""),"/","")," ","")

Doug
 
Upvote 0
Extra Credit-- :)

Two different scenarios that I thought might be very handy to include:
1) if there is not a second word in the source cell how to re-write the formula to avoid the "value" error

2) how to add another criteria in "I" if there is a third word, which would be a second space as the separator.

Thanks in advance for the assistance,

Regards,

Doug
 
Upvote 0
Hello,

to error check for one word

=IF(ISERROR(MID(A1,FIND(" ",A1)+1,1)),"",MID(A1,FIND(" ",A1)+1,1))

to find 3 words

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>=2,"Three words","less than 3 words")

don't know how you wish to incorporate these
 
Upvote 0
Try:

=CHOOSE((LEN(I146)-LEN(SUBSTITUTE(I146," ","")))+1,"",MID(I146,FIND(" ",I146)+1,1),MID(I146,FIND(" ",I146)+1,1)&MID(I146,FIND("#",SUBSTITUTE(I146," ","#",2))+1,1))

If only 1 word, then a null is returned

If two words, then 1st letter of second word returned

if three words, then 1st letter of second and third word concatenated returned.
 
Upvote 0
Hi Doug

In your formula

...&LEFT(I146,1)&MID(I146,FIND(" ",I146)+1,1)&...

you are concatenating the first letter of the first 2 words in I146.

Now if I understand correctly, you want to concatenate the first letter of the first 3 words in I146 (avoiding errors if less than 3 words exist).

Please try:

Code:
=LEFT(I146,1)&TRIM(MID(I146&" ",FIND(" ",I146&" ")+1,1))&TRIM(MID(I146&"  ",1+FIND("#", SUBSTITUTE(I146&"  "," ","#",2)),1))

Hope this helps
PGC

EDIT: posted the formula in code mode so that spaces are not eaten.
 
Upvote 0
Please notice that I have reposted the formula, this time in code mode. Some spaces were being eaten and the formula will not work without them.
PGC
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top