Help with MID and FIND function...!

[rishi.ssh]

Hi friends suppose i have a data in
A1= U.S Buyer id: 525-554 DONE
What i wanna want in B2=525-554
so i tried this formula in B2 =MID(A1,FIND("id:",A1)+4,LEN(A1)-FIND("id:",A1))
but i am getting 525-554 DONE in B2....
I dont want the done..how can i customize it for getting just what i need i mean starting from 5 till a space is encountered...?? Or just till 7 count..??? Please show me d correct way.
Thanks,
rishi.ssh

[NBVC]

try:

=TRIM(LEFT(TRIM(MID(A1,FIND("id:",A1)+3,255)),FIND(" ",TRIM(MID(A1,FIND("id:",A1)+3,255)))))

[rishi.ssh]

Ya this is working only when the desired value is accompanied with a space and other values...like if there is just Buyer id: 525-554 it returns error...?? And my each cells have vast amount of data more than 1500 characters....will it help..?

[NBVC]

Try this change:

=TRIM(LEFT(TRIM(MID(A2,FIND("id:",A2)+3,255)),FIND(" ",TRIM(MID(A2,FIND("id:",A2)+3,255))&" ")))

[vandan_tanna]

Or just till 7 count..??? Please show me d correct way.

if you just want 7 chars then

=MID(A1,FIND("id:",A1)+4,7)

[NBVC]

if you just want 7 chars then

=MID(A1,FIND("id:",A1)+4,7)

I didn't catch that... good catch.

[rishi.ssh]

hey thanlx vandan and Nvpm.. It worked...Friends can a macro be created that pulles and extract data from Similar cells and post it in corresponding Row with Customer id...like i got the way to extract id also my cells contains comission: -$value (it can be of any amount min of 2 and max of 4 characters) in Column B and C respectively....like in a1 both data are present ..i want that a macro could do this form me in Column B the Id and in Column C the comission against that Id...can this be possible..?? I will be grateful to you if u could make this happen.

[vandan_tanna]

Rishi:

Start a new thread for this new request. P lease attach a file in this new thread so we get a better idea of your needs.

If you are satisfied with the answer for your original post, then please mark this thread solved.

[rishi.ssh]

But the comission value can be of variable length...... Like -$1.5 or -$18.63 or -$212.36 which can also be followed by other things after a space ofcourse in that case i think i need to use that trim function.

[rishi.ssh]

Ok vandan..i will mark this thread as solved..thanks alot....and i guess i have already started a thread for it..but no replies since 2 days...

[xladept]

Hi rishi.ssh,

Try:

Please Login or Register  to view this content.

[rishi.ssh]

Hey Xladept.. thankx buddy.

[xladept]

Hey rishi.ssh...You're welcome!