Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data...
I need to search for then extract a specific portion of cell data. Below, I
have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
Ken,
I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A 1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
What do you mean by foolproof?
It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
Richard,
I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of
my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
Toppers -
I didn't mean to imply your formula didn't work, just that it didn't work correctly for me, in my spreadsheet. Below, I have list the results from your 1st and 2nd formulas, running it on the same list both times. As you can see, in my spreadsheet, the results aren't what I expected. It seems where not capturing all the numbers. Any thoughts? Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
Hi, This seems to work for your set of 6 records. Obviously it will nned modifying if there are any formats that don't meet the rule I explained in the last post. Watch the word wrap, this is all one formula. =IF(ISERROR(FIND("(",A1)),RIGHT(A1,LEN(A1)-FIND("x",A1)-1),SUBSTITUTE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"x","")) Rgds On Fri, 01 Sep 2006 20:06:14 GMT, "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
Ken,
Apology from me ... I had a similar problem but ASSUMED (and I know I shouldn't) it was simply due to me copying/pasting/"text-to-columns" from the NG so I simply manually deleted extra characters from the end of the strings. Further investigation indicates there are CHAR(160) (with CHAR(32) - blanks) characters at the end of most strings so these need to be removed. [Was the date extracted from a non-Excel source?) The following VBA macro does this and appears to clear the problem. To insert into the spreadsheet: Alt+F11 Right click in VBA Project window Insert==Module Copy/paste code below To use, highlight the range/column to be changed and RUN the macro. Sub ReplaceChr160() Selection.Replace What:=Chr(160), _ Replacement:=Chr(32), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub HTH "Ken" wrote: Toppers - I didn't mean to imply your formula didn't work, just that it didn't work correctly for me, in my spreadsheet. Below, I have list the results from your 1st and 2nd formulas, running it on the same list both times. As you can see, in my spreadsheet, the results aren't what I expected. It seems where not capturing all the numbers. Any thoughts? Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
...... or using a "helper" column use the following formula ...
=SUBSTITUTE(A1,CHAR(160),CHAR(32)) "Toppers" wrote: Ken, Apology from me ... I had a similar problem but ASSUMED (and I know I shouldn't) it was simply due to me copying/pasting/"text-to-columns" from the NG so I simply manually deleted extra characters from the end of the strings. Further investigation indicates there are CHAR(160) (with CHAR(32) - blanks) characters at the end of most strings so these need to be removed. [Was the date extracted from a non-Excel source?) The following VBA macro does this and appears to clear the problem. To insert into the spreadsheet: Alt+F11 Right click in VBA Project window Insert==Module Copy/paste code below To use, highlight the range/column to be changed and RUN the macro. Sub ReplaceChr160() Selection.Replace What:=Chr(160), _ Replacement:=Chr(32), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub HTH "Ken" wrote: Toppers - I didn't mean to imply your formula didn't work, just that it didn't work correctly for me, in my spreadsheet. Below, I have list the results from your 1st and 2nd formulas, running it on the same list both times. As you can see, in my spreadsheet, the results aren't what I expected. It seems where not capturing all the numbers. Any thoughts? Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
Toppers -
I really appreciate your time and patience. I'm beginning to get frustrated! I have tried everything you mentioned and it still appears not to be working for me. In an effort to try and make it easier for you to see what I'm getting, I have attached a excel spreadsheet containing the list I'm working with and showing how I applied your formulas. I'm sure it's working for you, therefore I'm certain I must be doing something wrong. It appears that wherever there is upper case "X" it doesn't seem to capture that but, it might be better if you could look at the whole list. Please take a look and let me know if you can help. Again, thanks for your help! //Ken "Toppers" wrote in message ... ..... or using a "helper" column use the following formula ... =SUBSTITUTE(A1,CHAR(160),CHAR(32)) "Toppers" wrote: Ken, Apology from me ... I had a similar problem but ASSUMED (and I know I shouldn't) it was simply due to me copying/pasting/"text-to-columns" from the NG so I simply manually deleted extra characters from the end of the strings. Further investigation indicates there are CHAR(160) (with CHAR(32) - blanks) characters at the end of most strings so these need to be removed. [Was the date extracted from a non-Excel source?) The following VBA macro does this and appears to clear the problem. To insert into the spreadsheet: Alt+F11 Right click in VBA Project window Insert==Module Copy/paste code below To use, highlight the range/column to be changed and RUN the macro. Sub ReplaceChr160() Selection.Replace What:=Chr(160), _ Replacement:=Chr(32), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub HTH "Ken" wrote: Toppers - I didn't mean to imply your formula didn't work, just that it didn't work correctly for me, in my spreadsheet. Below, I have list the results from your 1st and 2nd formulas, running it on the same list both times. As you can see, in my spreadsheet, the results aren't what I expected. It seems where not capturing all the numbers. Any thoughts? Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
Richard -
Thanks for your help. The formula you provided doesn't seem to work for me. Although it captures the number, it is also capturing the "x/X". Also, where there is no number to capture, it's providing a "#VALUE!" instead of a "1". Thanks again, //Ken "Richard Buttrey" wrote in message ... Hi, This seems to work for your set of 6 records. Obviously it will nned modifying if there are any formats that don't meet the rule I explained in the last post. Watch the word wrap, this is all one formula. =IF(ISERROR(FIND("(",A1)),RIGHT(A1,LEN(A1)-FIND("x",A1)-1),SUBSTITUTE(MID(A1 ,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"x","")) Rgds On Fri, 01 Sep 2006 20:06:14 GMT, "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
On Tue, 05 Sep 2006 22:35:15 GMT, "Ken" wrote:
Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It appears to me that what you want to do is capture the last numeric value in the string. If the rule is different, see if you can write it out. The following will give the results you have specified in your examples, using that rule. First, download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then, with your string in A1, use the formula: =REGEX.MID(A1,"\d+",-1) This will return the value as TEXT. If you want the value as a NUMBER, precede the formula with a double unary: =--REGEX.MID(A1,"\d+",-1) --ron |
#13
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
On Tue, 05 Sep 2006 20:53:16 -0400, Ron Rosenfeld
wrote: On Tue, 05 Sep 2006 22:35:15 GMT, "Ken" wrote: Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITU TE(SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It appears to me that what you want to do is capture the last numeric value in the string. If the rule is different, see if you can write it out. The following will give the results you have specified in your examples, using that rule. First, download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then, with your string in A1, use the formula: =REGEX.MID(A1,"\d+",-1) This will return the value as TEXT. If you want the value as a NUMBER, precede the formula with a double unary: =--REGEX.MID(A1,"\d+",-1) --ron I missed one. What do you want returned for: 5054-SUA-US I'm assuming you want a blank. If that is the case, change the formula to: =REGEX.MID(A1,"(?<=\D)\d+",-1) --ron |
#14
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
Ken,
No attachment .... but has it been solved using Ron's solution? Sorry to have caused you so much frustration! [For my own curiosity, can you e-mail your spreadsheet as I am curious to find out why it works for me ((using the NG data) and not you]. "Ken" wrote: Toppers - I really appreciate your time and patience. I'm beginning to get frustrated! I have tried everything you mentioned and it still appears not to be working for me. In an effort to try and make it easier for you to see what I'm getting, I have attached a excel spreadsheet containing the list I'm working with and showing how I applied your formulas. I'm sure it's working for you, therefore I'm certain I must be doing something wrong. It appears that wherever there is upper case "X" it doesn't seem to capture that but, it might be better if you could look at the whole list. Please take a look and let me know if you can help. Again, thanks for your help! //Ken "Toppers" wrote in message ... ..... or using a "helper" column use the following formula ... =SUBSTITUTE(A1,CHAR(160),CHAR(32)) "Toppers" wrote: Ken, Apology from me ... I had a similar problem but ASSUMED (and I know I shouldn't) it was simply due to me copying/pasting/"text-to-columns" from the NG so I simply manually deleted extra characters from the end of the strings. Further investigation indicates there are CHAR(160) (with CHAR(32) - blanks) characters at the end of most strings so these need to be removed. [Was the date extracted from a non-Excel source?) The following VBA macro does this and appears to clear the problem. To insert into the spreadsheet: Alt+F11 Right click in VBA Project window Insert==Module Copy/paste code below To use, highlight the range/column to be changed and RUN the macro. Sub ReplaceChr160() Selection.Replace What:=Chr(160), _ Replacement:=Chr(32), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub HTH "Ken" wrote: Toppers - I didn't mean to imply your formula didn't work, just that it didn't work correctly for me, in my spreadsheet. Below, I have list the results from your 1st and 2nd formulas, running it on the same list both times. As you can see, in my spreadsheet, the results aren't what I expected. It seems where not capturing all the numbers. Any thoughts? Column "A" (raw data) 1st Formula Results 2nd Formula Results 8675-US (59) 59 1 (should be 59) 8675-EU x 5 #VALUE! 5 6330-02(18) 18 1 (should be 18) 6330-02CE (x10 ) #VALUE! 1 (should be 10) 6305-02 (1) 1 1 58516 (x117) #VALUE! 1 (should be 117) 6312-05(X30) #VALUE! 1 (should be 30) 604-800006-002 #VALUE! 1 5054-SUA-US #VALUE! 1 6312-05(X20 ) #VALUE! 1 (should be 20) 6420-05(30) 30 1 (should be 30) 6420-05 ( 9 ) 9 1 (should be 9) I really appreciate your help! Thanks, //Ken "Toppers" wrote in message ... Sorry but it DOES capture (X4), (X10), (X117). These are a direct copy of my results using the supplied formula. 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 It doesn't capture the situation where no quantity was present but this will: =IF(ISERROR(VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))),1,VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A8,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A8,"("," "),"x"," ")),255)))) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 58517 1 HTH "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#15
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell data.
Ken,
That is odd since it works perfectly for me. Can you confirm that you have put your original data in A1:A7, for example the first string in A1 is 700001103 (x4) and then copied my formula down B1:B7? When you suggest there is ' no number to capture', that suggests that there is another category of format not covered by your original 7 examples, since each of these has a number either at the end, as per the 3rd example or within the brackets. Can you post the format of this additional string. Rgds Ken wrote: Richard - Thanks for your help. The formula you provided doesn't seem to work for me. Although it captures the number, it is also capturing the "x/X". Also, where there is no number to capture, it's providing a "#VALUE!" instead of a "1". Thanks again, //Ken "Richard Buttrey" wrote in message ... Hi, This seems to work for your set of 6 records. Obviously it will nned modifying if there are any formats that don't meet the rule I explained in the last post. Watch the word wrap, this is all one formula. =IF(ISERROR(FIND("(",A1)),RIGHT(A1,LEN(A1)-FIND("x",A1)-1),SUBSTITUTE(MID(A1 ,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1),"x","")) Rgds On Fri, 01 Sep 2006 20:06:14 GMT, "Ken" wrote: Richard, I think your on to something here. Toppers response worked great except, as you pointed out, it only captures the numbers in (##) or ( ## ). It does not capture numbers in (x##) or ( x## ) or (X##), etc... Ultimately, I need to capture all scenarios. If the part number does not have a quantity at the end (usually indicated by the "x/X" or "(" ), then the result should be the number "1", indicating a single part number. EXAMPLE: Cell "A" 700001103 (x4) (part number) 700001103 (quantity of) (x4) < (i need just the number indicating the quantity, regardless of what it is/is not wrapped in) 4 Is this possible to do? Thanks again, to you and Toppers //Ken "Richard Buttrey" wrote in message ... What do you mean by foolproof? It is foolproof for the limited set of 6 records you give as an example. Have you any other formats that are not covered by the rules covered. i.e include characters between "(" and ")" unless the character is "x" or " " (space) or if null result include all characters following the last space Regards On Fri, 1 Sep 2006 11:27:02 -0700, Toppers wrote: Ken, I tested this in your sample and it appears to work BUT I cannot guarantee it is "foolproof"! =VALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"("," "),"x"," "),")",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"("," "),"x"," ")),255))) HTH "Ken" wrote: I need to search for then extract a specific portion of cell data. Below, I have provided an example of what I'm trying to do. Column "A" (raw data) Column "B" (end result) 700001103 (x4) 4 8675-US (59) 59 8675-EU x 5 5 6330-02(18) 18 6330-02CE (x10 ) 10 6305-02 (1) 1 58516 (x117) 117 I hope someone can help me. Thanks. //Ken __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#16
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to search for then extract a specific portion of cell d
On Tue, 05 Sep 2006 21:11:27 -0400, Ron Rosenfeld
wrote: I missed one. What do you want returned for: 5054-SUA-US I'm assuming you want a blank. If that is the case, change the formula to: =REGEX.MID(A1,"(?<=\D)\d+",-1) The following should be more "robust" depending on what you want. It's rule is that it will return the last set of digits in the string only if they are NOT followed by other alphanumeric characters. =REGEX.MID(A1,"\d+(?!.*\w)",-1) So this formula will return a null string ("") given: 5054-123-US The first formula: =REGEX.MID(A1,"(?<=\D)\d+",-1) will return "123" --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract portion of cell contents | Excel Worksheet Functions | |||
Extract portion of a cell | Excel Discussion (Misc queries) | |||
Extract a specific portion of text as new cell value | Excel Discussion (Misc queries) | |||
Extract portion of formula resident in a cell | Excel Worksheet Functions | |||
I need to search for then extract a specific portion of cell data... | Excel Worksheet Functions |