How to find & delete a particular pattern of text from values of a

[Mansa]

I have a columns having values like 50200986, FL0050200987, FL000050200234 etc.
I want to find and remove FL00, FL000 etc and keep only the values which are
trimmed of FL, FL00 or FL000 etc.

Please help .

Manish

 

[Mike H]

Maybe


This assumes the characters after the FL are zeroes

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1))

Mike

 

[Mansa]

Thanks David and Mike for your analysis, but sorry it not working.

I'll again simply my doubt.I have a column which has following values :

50099532
50099532
50099532
FL0050069061
FL0050069061
FL0050069061
FL0050069061
FL0050069061

I need to remove any occurance of “FL00â€, “FL000â€, “FL:000â€, “FL 000â€, “FL
00â€, “000†from values in above column.

E.g. “FL00050200986†becomes “â€50200986†after removal.

Hope this will make my query more clear.

Please extend your help solve this one.

Many thanks again!

Regds,
Mansa

 

[David Biddulph]

"it not working" is this week's leader in the "unhelpful description of a
problem" competition.

What result did my formula give? What result did you expect?

 

[Mansa]

My Bad David, I never meant to challenge your intelligence. Your formula is
giving me string having FL00 but I need to replace FL part plus leading zero
till number '5' from any string which preceds by FL00 or FL0000 etc. Sorry If
am not using your formula correctly.

 

[Mike H]

Mansa,

My formula should do what you want if the digits after FL are zeroes or the
letter O in fact that's the only reason I posted it because it works for both
as apposed to the other solution you have. In what way isn't it working?

Mike

 

[David Biddulph]

I'll try again:

What value did you feed into my formula?
What value did you get out of my formula?
What value did you expect to get?

 

[Mansa]

Hi David,Pls find my response below:

What value did you feed into my formula? As I have 59565 records for my column (A1), I copied your formula in cell A2 and raggedit along entire column. This is what I copied : IF(LEFT(A2,2)="FL",--RIGHT(A2,LEN(A2)-2),A2)

What value did you get out of my formula? for records without FL as prefix, its working perfectly. I mean for eg, for record "50069061", its correctly giving me "50069061" and for record FL0050069061, its giving me 50069061. Thats fine, but for record like FL:0050069060, its giving me error.

What value did you expect to get? As mentioned earlier, I need to remove any/all occurances of “FL00â€, “FL000â€, “FL:000â€, “FL 000â€, “FL00â€, “000†from records in my column. I'll bet that your formula is doing the trick and we just need to add few more parameters to accomodate other criterias like FL:00, FL:000 etc.

Hope this helps!

Thanks,
MAnsa

 

[Mansa]

Hi Mike, thanks a bunch for your response.

I have FL and either tow or three or four zeroes or FL:00 or FL:0000 etc but
no Letter 'O'. I copied
MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),LEN(A1)) and dragged
thsi one to all 59565 records in my column A2. I strongly agree that either I
am not customizing your formula correctly or else.

Am working!!

Thx
MAnsa