Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter text
Am building a sheet to quickly do a price analysis of materials, as the prices are updated almost monthly I would prefer to copy and past the data into excel which works, only one of the colums contains all information which I need to break up for excell to use it for a vlookup function.:Bgr I have the following text in a column. 90X6M DUROFLO UPVC P/PIPE CL9 140X6M DUROFLO UPVC P/PIPE CL12 I want to exctract the 90 and the 9 for the first one and the second 140 and 12 etc, all values are similar in writing but just contain different numbers. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103792 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter text
=LEFT(A1,SEARCH("X",A1)-1)
=RIGHT(A1,LEN(A1)-SEARCH("CL",A1)-1) "willemeulen" wrote: Am building a sheet to quickly do a price analysis of materials, as the prices are updated almost monthly I would prefer to copy and past the data into excel which works, only one of the colums contains all information which I need to break up for excell to use it for a vlookup function.:Bgr I have the following text in a column. 90X6M DUROFLO UPVC P/PIPE CL9 140X6M DUROFLO UPVC P/PIPE CL12 I want to exctract the 90 and the 9 for the first one and the second 140 and 12 etc, all values are similar in writing but just contain different numbers. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103792 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter text
If there is *always* an "X" after the first number and an CL in front of the
last number, then 'Teethless mama' has given you the answer you need. However, if this will not always be the case, then you can try the following: Return leading number from a cell... =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99))) Return the ending number from a cell... =LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99))) -- Rick (MVP - Excel) "willemeulen" wrote in message ... Am building a sheet to quickly do a price analysis of materials, as the prices are updated almost monthly I would prefer to copy and past the data into excel which works, only one of the colums contains all information which I need to break up for excell to use it for a vlookup function.:Bgr I have the following text in a column. 90X6M DUROFLO UPVC P/PIPE CL9 140X6M DUROFLO UPVC P/PIPE CL12 I want to exctract the 90 and the 9 for the first one and the second 140 and 12 etc, all values are similar in writing but just contain different numbers. Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103792 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My filter does not pick up all text | Excel Discussion (Misc queries) | |||
filter in text | Excel Discussion (Misc queries) | |||
Filter text in a column by its Indent, to remove certain text | Excel Discussion (Misc queries) | |||
Keep text filter away numbers | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |