Hi,

I need to extract the first character after the second space in a string to create a reference number.

I seem to have partially figured it out but my formula picks the second space and the character I want to pick

please help.

example

data

customer name

AN EXAMPLE HERE

formula

="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1),2)&"/"&MONTH(C5)&YEAR(C5)&"/01"

result

ZMM/AE H/92017/01

instead of

ZMM/AEH/92017/01

Thanks in advance

Hi

Your formula is close, but not quite there. Here's your formula:

="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1),2)&"/"&MONTH(C5)&YEAR(C5)&"/01"

Here's the correct formula. I've highlighted the corrections:

="ZMM/"&LEFT(F5,1)&MID(F5,FIND(" ",F5)+1,1)&MID(F5,FIND(" ",F5,FIND(" ",F5)+1)

+1,1)&"/"&MONTH(C5)&YEAR(C5)&"/01"Your original formula was taking two characters including the third space. This version takes one character starting with the first character after the third space.

@millionleaves

It works. thanks !!

## Add new comment