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

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

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

=IF(LEFT(A2,2)="FL",--RIGHT(A2,LEN(A2)-2),A2)
--
David Biddulph

"Mansa" wrote in message
...
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

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

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" wrote:

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

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

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

"Mike H" wrote:

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" wrote:

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

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

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

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
"Mansa" wrote:

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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

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?
--
David Biddulph

"Mansa" wrote in message
...
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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

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

"David Biddulph" wrote:

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?
--
David Biddulph

"Mansa" wrote in message
...
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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

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


"Mike H" wrote:

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
"Mansa" wrote:

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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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

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

In your original question you were talking of getting rid of FL, FL00 or
FL000, etc.

I see that you've now added extra cases such as "FL:000", "FL 000".

That wasn't what I was answering, so my formula wouldn't work in those
cases.
--
David Biddulph

"Mansa" wrote in message
...
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

"David Biddulph" wrote:

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?
--
David Biddulph

"Mansa" wrote in message
...
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.

"David Biddulph" wrote:

"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?
--
David Biddulph

"Mansa" wrote in message
...
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

"Mike H" wrote:

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" wrote:

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