| 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| 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 | |||
Linear Mode
