| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| query with formula/macro to find text in worksheet and delete it | Excel Worksheet Functions | |||
| find text and delete rows. | Excel Discussion (Misc queries) | |||
| Identifying A Pattern Of Values Meeting Specific Criteria | Excel Discussion (Misc queries) | |||
| find cell that contains text and delete entre row | Excel Discussion (Misc queries) | |||
| Function to find duplicate values, then delete | Excel Worksheet Functions | |||
Hybrid Mode
