Seperate Numbers from Text

G

G R White

Hi
I have 1200 lines of text and numbers in the form below


Gross Sales 1200 1500 2400 1100
Garden 40 50 65 75
Plants 20 25 35 45

Each entry is in 1 cell , how can I examine each cell , find the end of text
and move any subsequent numbers to the next cell right, ( I can then use
text to columns to seperate the numbers)

Regards
GW
 
A

AlfD

Hi!

I would use Text to Columns right away.
I know this leaves some tidying up to do, because of the differen
number of words preceding the numbers.

I would add a helper column numbered 1 to 1200 or so. This is t
re-sort the list after the next steps are done.

Now sort on the columns which have mixed text and numbers. Excel wil
ask you what to do about numbers which look like text: tell it to trea
anything which looks like a number as a number.
Now your misfits will be grouped and visible and can be dragged th
requisite number of columns right to align the figures.

Re-sort using the helper column.

Al
 
G

G R White

Hi Alfd
Have tried this but can't find option sort Text and Numbers
using Data/Sort
Regards GW
 
H

hgrove

G R White wrote...
I have 1200 lines of text and numbers in the form below

Gross Sales 1200 1500 2400 1100
Garden 40 50 65 75
Plants 20 25 35 45

Each entry is in 1 cell , how can I examine each cell , find the
end of text and move any subsequent numbers to the next cell
right, ( I can then use text to columns to seperate the numbers)
Click to expand...

If you never have anything other than decimal numerals and spaces t
the right of the first decimal numeral, you could split out the leadin
text using the following formulas. Note: your original text is assume
to be in column A; columns B and C will hold leading text and followin
numbers, respectively.

B1:
=TRIM(MID(A1,1,MATCH(TRUE,ISNUMBER(-MID(A1,
ROW(INDIRECT("1:1024")),1)),0)-1))

C1:
=TRIM(MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,
ROW(INDIRECT("1:1024")),1)),0),1024)
 
H

hgrove

AlfD wrote...
...
I would add a helper column numbered 1 to 1200 or so. This is
to re-sort the list after the next steps are done.
Click to expand...
...

What are you talking about?!

Why would the OP sort the data? What benefit would be gained even if
there were rows/records in which there was no leading text?
 
A

AlfD

Hi!

May be a version difference. A numbers/text warning comes up i
Excel2003.

Ignore its absence: do the sort. What matters is that it groups lik
with like. Order per se doesn't matter.

For the benefit of (the rather peremptory) hgrove: the sorting is s
that the next bit - spotting and moving in batches - is made easier
quicker and more thorough. I generally find ordered data much better t
deal with than a random scatter.

Al
 
P

Peo Sjoblom

hgrove > said:
G R White wrote...

If you never have anything other than decimal numerals and spaces to
the right of the first decimal numeral, you could split out the leading
text using the following formulas. Note: your original text is assumed
to be in column A; columns B and C will hold leading text and following
numbers, respectively.

B1:
=TRIM(MID(A1,1,MATCH(TRUE,ISNUMBER(-MID(A1,
ROW(INDIRECT("1:1024")),1)),0)-1))

C1:
=TRIM(MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,
ROW(INDIRECT("1:1024")),1)),0),1024))
Click to expand...

LOL

Never expected you in the Excel forum!?

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
H

hgrove

Peo Sjoblom wrote...
...
Never expected you in the Excel forum!?
Click to expand...
...

Newsranger sleeps with the dodos. This is only temporary. Besides
excelforum is still better than the CDO
 
H

hgrove

AlfD wrote...
...
For the benefit of (the rather peremptory) hgrove: the sorting is
so that the next bit - spotting and moving in batches - is made
easier, quicker and more thorough. I generally find ordered data
much better to deal with than a random scatter.
Click to expand...

OK, but sorting is unnecessary. Start with the following in A1:A4.

a b c 1 2 3
a b 1 2 3 4
a 1 2 3
a b c d 1 2 3 4 5

Select A1:A4 and run Data > Text to columns, choose Delimited, and mak
sure Space is on of the delimiters before clicking on Finish. The dat
should now be in separate columns like so.

a__b__c__1__2__3
a__b__1__2__3__4
a__1__2__3
a__b__c__d__1__2__3__4__5

Select A1:D4, press [F5], click on Special..., select Constants an
uncheck all types except Number (checkboxes appear below Formula bu
apply to Constants as well), click OK, then run Insert > Cells...
choose Shift cells right, and click OK
 
H

hgrove

hgrove wrote...
...
OK, but sorting is unnecessary. Start with the following in A1:A4.
Click to expand...
...

I forgot to mention that any procedure that involves using Text t
Columns first also requires a general concatenation operation for th
leftmost columns to reproduce the leading text intact. That'
nontrivial and in & of itself is enough to warn any sane person *NOT
to run Text to Columns first
 
A

AlfD

hgrove:

Thank you for that: I like your routine.

Didn't notice the point at which you concatenated the leftmos
columns?

Al
 
H

hgrove

AlfD wrote...
Didn't notice the point at which you concatenated the leftmost
columns?
Click to expand...

Wouldn't bother. I'd recommend the OP use the formulas in my firs
response in this thread, copy them and paste special as values on to
of them, then run Data > Text to Columns only on the column of numbers
Seems like the OP knows how to handle the details and only needed th
formulas, which is why I didn't go into excruciating detail in my firs
response
 
Top