Finding number between two characters in cell.

[paularthur90]

Hi,

I am trying to find the number (could be 3 or 4 numbers long) after the 2nd '#' and before the 3rd '#'.

0000434269#In#1253###20150622#075650.wav

The number I want is 1253.

Thanks,
Paul

[quekbc]

Try this...

=MID(H7,FIND("@",SUBSTITUTE(H7,"#","@",2))+1,FIND("@",SUBSTITUTE(H7,"#","@",3))-FIND("@",SUBSTITUTE(H7,"#","@",2))-1)

[zbor]

This will return you text 1234 (not number but text):

TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(A1,"#",REPT(" ",255),2),255,255),"#",REPT(" ",255)),255))

If you want number you need to multiply this result by 1*formula but this will return you erro in case there si something else than number.
For example: 0000434269#In#12C3###20150622#075650.wav will cause error while previous wont so I didn't multiply it.

[MarvinP]

Hi Paul,

Try this one

=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))

See the attached.

[paularthur90]

Thank you everyone for all your help.

Got it all sorted