Extract first character after every space

[Ratso]

Hi,

Me again

I have a text string in cell B3 with a name and all I want to do is extract the first letter after every space with a formula but not the title.

So for example:

Mr Davy Jones should return as DJ

I have the below formula that works to a point

Please Login or Register  to view this content.

But it doesn't work if I have a double barrel name such as Mr Davy Jones Smith and returns only DJ still and ideally it should return as DJS.

Any ideas?

[avk]

I am little bit closer. If your name as : Mr.Davy Jones [Mr.start Name without space]
A1 is : Mr.Davy Jones
B1 :

Please Login or Register  to view this content.

[XLent]

If we assume you're running 2010 per profile, another option might be to do something like the below:

1. create a named range, with A1 active cell {assumed to be first description}:

Please Login or Register  to view this content.

2. in B1, i.e. where you want to return value

Please Login or Register  to view this content.

if you need to increase from 5 letters change the ROW variable, and then add a further INDEX to the concatenation.

if you're using XL2016 you can negate all of the above with CONCAT, e.g.;

Please Login or Register  to view this content.

all the while notwithstanding Atul's point, that you may need to handle a variety of titles / formats which may render the above flawed.

[AlKey]

Try this one
Enter formula in B1 and copy down

Please Login or Register  to view this content.

v	A	B
1	Mr Davy Jones	DJ
2	Mis Mary Jones	MJ
3	Mr Andrew J Cort	AJC

[Ratso]

Hi Atul,

Unfortunately there isn't a "." after the title and adding this to the report which has thousands of lines isn't something that I would like the end user to be doing.

Also it is picking up the M for Mr so my result is returning MDJ for Mr Davy Jones and Mr Davy Jones Smith.

Thanks for trying though

[Ratso]

Hi XLent,

This would be far to complicated for the end users to complete hence why I just wanted a formula to give them so they can paste and copy down to the last row.

I'm getting this strange feeling it cannot be done

Thanks for the response though

[Ratso]

Hi AlKey,

This appears to work

Can you kindly explain how it is working or point me in the right direction?

[AlKey]

Hi Ratso,

The formula I suggested is simply concatenation of few formulas joint with &.
1. =MID(A1,FIND(" ",A1)+1,1)
2. LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50)))
3. LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),150,50))

[Ratso]

Thanks Alkey,

So if I am getting this correctly you are recreating the " " 50 times and then asking the formula to start from 100 characters in then 150 and so on and so forth?

[AlKey]

Hi Ratso,

The formula I suggested is simply concatenation of few formulas joint with &.
1. =MID(A1,FIND(" ",A1)+1,1)
2. LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),100,50)))
3. LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),150,50))

Yes, you are correct. For the forth one simply change the last formula:
LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),200,50))[/QUOTE]