Extract first character after every space

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

=IFERROR(MID(B3,FIND(" ",B3)+1,1),"")&IFERROR(MID(B3,FIND(" ",B3,FIND(" ",B3,1)+1)+1,1),"")

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?

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

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}:

Formula:

Name: _LT

RefersTo: =LEFT(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),1+100*ROW($1:$5),100)))

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

Formula:

=INDEX(_LT,1)&INDEX(_LT,2)&INDEX(_LT3)&INDEX(_LT4)&INDEX(_LT,5)

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.;

Formula:

B1: 

=CONCAT(INDEX(LEFT(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),1+100*ROW($1:$5),100))),0))

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

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

Hi AlKey,

This appears to work

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

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?