Newbie question

[Vince]

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]

Hi Vince

Have you tried 'text to columns'
If you select the column that has Carton cheese 12x350oz in it go t
the 'data' menu select text to columns, select 'delimited' click o
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
go and fiddle around with it and see how you go

 

[Biff]

Hi!

Try this:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),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]

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?

 

[Dave Peterson]

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]

Hi!

Try this:

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

 

[Vince]

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

 

[Biff]

Works for me. See this screencap:

http://img136.imageshack.us/img136/6926/samplexyq2.jpg

Biff

 

[wmack]

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

 

[Biff]

That only works if there are 1 or 2 digits. Why use 2 formulas? Those could
be combined into 1.

Biff

 

[Vince]

Works for me. See this screencap:

http://img136.imageshack.us/img136/6926/samplexyq2.jpg

Biff

You're the man! Brilliant, thank you very much!

 

[Dave Peterson]

I meant a total of 2 characters--one asterisk and one spacebar.

Same with the x*--one x and one asterisk.

 

[Biff]

You're welcome!

Biff

You're the man! Brilliant, thank you very much!