Newbie question
Hello everyone,
I have data on a number of rows that looks like this (all in the same cell) Carton cheese 12x350oz Carton wine 8x500ml Carton cheddar 24x200oz What I want is to just extract the 12, 8 and 24 to appear alongside, in a separate column - I have tried text to colums but unsuccesfully, any thoughts? The text length before the number is uneven, I guess that' s where my problem is?? Thanks |
Newbie question
Hi Vince Have you tried 'text to columns' If you select the column that has Carton cheese 12x350oz in it go to the 'data' menu select text to columns, select 'delimited' click on next and then type in x in the 'other' section and click on finish. This will then move everything after the x to a new column. Give it a go and fiddle around with it and see how you go! -- Steel Monkey ------------------------------------------------------------------------ Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051 View this thread: http://www.excelforum.com/showthread...hreadid=573622 |
Newbie question
Hi!
Try this: =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),FIND("x",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))* 1 Assumes every entry has some numbers and there is only the one "x". Biff "Vince" wrote in message ups.com... Hello everyone, I have data on a number of rows that looks like this (all in the same cell) Carton cheese 12x350oz Carton wine 8x500ml Carton cheddar 24x200oz What I want is to just extract the 12, 8 and 24 to appear alongside, in a separate column - I have tried text to colums but unsuccesfully, any thoughts? The text length before the number is uneven, I guess that' s where my problem is?? Thanks |
Newbie question
Steel Monkey wrote: Hi Vince Have you tried 'text to columns' If you select the column that has Carton cheese 12x350oz in it go to the 'data' menu select text to columns, select 'delimited' click on next and then type in x in the 'other' section and click on finish. This will then move everything after the x to a new column. Give it a go and fiddle around with it and see how you go! Yep, tried it but my problem is that the text varies in length so when I do text to colums, the arrows can't pick up the number, see below: Cask wine |12|x200mls Case of cheese |12|x200oz Txs Steel? or monkey? |
Newbie question
One more way.
Insert a column to the right of the original column Copy the origin column's data into that adjacent column. Select that helper column edit|Replace what: *_ (asterisk, then spacebar -- two characters) with: (leave blank) replace all And once more edit|Replace what: x* (x character, then asterisk -- two characters) with: (leave blank) replace all Vince wrote: Hello everyone, I have data on a number of rows that looks like this (all in the same cell) Carton cheese 12x350oz Carton wine 8x500ml Carton cheddar 24x200oz What I want is to just extract the 12, 8 and 24 to appear alongside, in a separate column - I have tried text to colums but unsuccesfully, any thoughts? The text length before the number is uneven, I guess that' s where my problem is?? Thanks -- Dave Peterson |
Newbie question
Biff wrote: Hi! Try this: Biff, looked good but didn't work when I simply cut and paste into Excel, thanks anyway! |
Newbie question
Dave Peterson wrote: One more way. Insert a column to the right of the original column Copy the origin column's data into that adjacent column. Select that helper column edit|Replace what: *_ (asterisk, then spacebar -- two characters) with: (leave blank) replace all And once more edit|Replace what: x* (x character, then asterisk -- two characters) with: (leave blank) replace all Thanks Dave, with this line what: *_ (asterisk, then spacebar -- two characters) do you mean to simply type in * followed by hitting space bar twice?? or type * then spacebar then type two characters?? |
Newbie question
Works for me. See this screencap:
http://img136.imageshack.us/img136/6926/samplexyq2.jpg Biff "Vince" wrote in message ps.com... Biff wrote: Hi! Try this: Biff, looked good but didn't work when I simply cut and paste into Excel, thanks anyway! |
Newbie question
you could also add two rows, First row would be: =SEARCH("x",A1) Next Row would be =TRIM(MID(A1,(B1-2),2)) Then just hid the row with the search strind in it. -- wmack ------------------------------------------------------------------------ wmack's Profile: http://www.excelforum.com/member.php...o&userid=37801 View this thread: http://www.excelforum.com/showthread...hreadid=573622 |
Newbie question
That only works if there are 1 or 2 digits. Why use 2 formulas? Those could
be combined into 1. Biff "wmack" wrote in message ... you could also add two rows, First row would be: =SEARCH("x",A1) Next Row would be =TRIM(MID(A1,(B1-2),2)) Then just hid the row with the search strind in it. -- wmack ------------------------------------------------------------------------ wmack's Profile: http://www.excelforum.com/member.php...o&userid=37801 View this thread: http://www.excelforum.com/showthread...hreadid=573622 |
Newbie question
Biff wrote: Works for me. See this screencap: http://img136.imageshack.us/img136/6926/samplexyq2.jpg Biff You're the man! Brilliant, thank you very much! |
Newbie question
I meant a total of 2 characters--one asterisk and one spacebar.
Same with the x*--one x and one asterisk. Vince wrote: Dave Peterson wrote: One more way. Insert a column to the right of the original column Copy the origin column's data into that adjacent column. Select that helper column edit|Replace what: *_ (asterisk, then spacebar -- two characters) with: (leave blank) replace all And once more edit|Replace what: x* (x character, then asterisk -- two characters) with: (leave blank) replace all Thanks Dave, with this line what: *_ (asterisk, then spacebar -- two characters) do you mean to simply type in * followed by hitting space bar twice?? or type * then spacebar then type two characters?? -- Dave Peterson |
Newbie question
You're welcome!
Biff "Vince" wrote in message oups.com... Biff wrote: Works for me. See this screencap: http://img136.imageshack.us/img136/6926/samplexyq2.jpg Biff You're the man! Brilliant, thank you very much! |
All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com