FIND MID & LEFT Functions

3 posts / 0 new
Last post
FIND MID & LEFT Functions

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
 

Here's the correct formula

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.

Thank You very much

@millionleaves
It works. thanks !!

Add new comment