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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Vince]

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?

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

[Vince]

Biff wrote:
> Hi!
>
> Try this:
>


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

[Vince]

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

[Biff]

Works for me. See this screencap:

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

Biff

"Vince" <[email protected]> wrote in message
news:[email protected]...
>
> Biff wrote:
>> Hi!
>>
>> Try this:
>>
>
>
> Biff, looked good but didn't work when I simply cut and paste into
> Excel, thanks anyway!
>

[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

"wmack" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Vince]

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!

[Dave Peterson]

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

[Biff]

You're welcome!

Biff

"Vince" <[email protected]> wrote in message
news:[email protected]...
>
> 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!
>