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

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

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

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?

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

Biff wrote:
Hi!

Try this:



Biff, looked good but didn't work when I simply cut and paste into
Excel, thanks anyway!

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??

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!

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

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

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!

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

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!