|
how do I extract only certain data from a cell?
Office 2003, Win XP Pro.
I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
how do I extract only certain data from a cell?
Try this:
=MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&1516171 81920000)),5) And copy down as needed. This should work - as long as there are no spaces within the actual 5 digit invoice number itself. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Stan in South Africa" <me@there wrote in message ... Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
how do I extract only certain data from a cell?
Thank You!
"Ragdyer" wrote in message ... Try this: =MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&1516171 81920000)),5) And copy down as needed. This should work - as long as there are no spaces within the actual 5 digit invoice number itself. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Stan in South Africa" <me@there wrote in message ... Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
how do I extract only certain data from a cell?
One possible problem... an entry like this: Oops 183AB18801
Rick "Ragdyer" wrote in message ... Try this: =MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&1516171 81920000)),5) And copy down as needed. This should work - as long as there are no spaces within the actual 5 digit invoice number itself. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Stan in South Africa" <me@there wrote in message ... Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
how do I extract only certain data from a cell?
On Sun, 6 Jul 2008 06:37:58 +0200, "Stan in South Africa" <me@there wrote:
Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. You can do it with a User Defined Function: To enter the function, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use this, enter a formula of the type: =Invoice(cell_ref) into some cell. (cell_ref could also be the actual string itself. If a value of 15000-20000 does not exist, the function will return a #VALUE! error, although a more descriptive result could be substituted. =================================== Option Explicit Function Invoice(str As String) Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "(1[5-9]\d{3})|(200000)" If re.test(str) = True Then Set mc = re.Execute(str) Invoice = mc(0).Value Else Invoice = CVErr(xlErrValue) End If End Function =================================== --ron |
how do I extract only certain data from a cell?
You're welcome ... but by now you probably see the shortcomings.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Stan in South Africa" <me@there wrote in message ... Thank You! "Ragdyer" wrote in message ... Try this: =MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&1516171 81920000)),5) And copy down as needed. This should work - as long as there are no spaces within the actual 5 digit invoice number itself. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Stan in South Africa" <me@there wrote in message ... Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
how do I extract only certain data from a cell?
You're right!
Though it was probably enough to get the OP started. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... One possible problem... an entry like this: Oops 183AB18801 Rick "Ragdyer" wrote in message ... Try this: =MID(A1,MIN(FIND({15,16,17,18,19,20000},A1&1516171 81920000)),5) And copy down as needed. This should work - as long as there are no spaces within the actual 5 digit invoice number itself. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Stan in South Africa" <me@there wrote in message ... Office 2003, Win XP Pro. I am presented with a worksheet containing dozens of cells in which is contained data of different lengths. eg Zippohat 52131123Sa011880702 Baker 18164 Renatsainv17247 15592 Inv Each contains an invoice number, as follows: 18807 18164 17247 15592 These invoice numbers range between 15000 and 20000 Is there any way to extract only the invoice number from each cell. |
| All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com