Find First Letter in second word and add to formula

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966

ADVERTISEMENT

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
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,966
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
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows

ADVERTISEMENT

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,508
Messages
5,676,270
Members
419,617
Latest member
Shane50GT

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
Top