Seperate Numbers from Text

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


0
grwhite (2)
7/6/2004 9:59:56 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
613 Views

Similar Articles

[PageSpeed] 58

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

--
Message posted from http://www.ExcelForum.com

0
7/6/2004 10:28:37 PM
Hi Alfd
Have tried this but can't find option sort Text and Numbers
using Data/Sort
Regards GW
"AlfD >" <<AlfD.18zu5n@excelforum-nospam.com> wrote in message
news:AlfD.18zu5n@excelforum-nospam.com...
> Hi!
>
> I would use Text to Columns right away.
> I know this leaves some tidying up to do, because of the different
> number of words preceding the numbers.
>
> 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.
>
> Now sort on the columns which have mixed text and numbers. Excel will
> ask you what to do about numbers which look like text: tell it to treat
> anything which looks like a number as a number.
> Now your misfits will be grouped and visible and can be dragged the
> requisite number of columns right to align the figures.
>
> Re-sort using the helper column.
>
> Alf
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
grwhite (2)
7/6/2004 10:59:04 PM
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)

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)

--
Message posted from http://www.ExcelForum.com

0
7/6/2004 10:59:08 PM
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.
...

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?


---
Message posted from http://www.ExcelForum.com/

0
7/6/2004 11:02:18 PM
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

--
Message posted from http://www.ExcelForum.com

0
7/6/2004 11:28:58 PM
"hgrove >" <<hgrove.18zvki@excelforum-nospam.com> wrote in message
news:hgrove.18zvki@excelforum-nospam.com...
> 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)
>
> 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))
>
>

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)


0
terre081 (3244)
7/6/2004 11:31:21 PM
Peo Sjoblom wrote...
...
>Never expected you in the Excel forum!?
...

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

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 12:28:47 AM
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.

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

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 12:41:16 AM
hgrove wrote...
...
>OK, but sorting is unnecessary. Start with the following in A1:A4.
...

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

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 12:44:59 AM
hgrove:

Thank you for that: I like your routine.

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

Al

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 11:19:08 PM
AlfD wrote...
>Didn't notice the point at which you concatenated the leftmost
>columns?

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

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 11:42:42 PM
Reply:

Similar Artilces:

Help with seperating data?
Hello I was wondering if anyone knows a way of seperating chunks of data into specific lines. i.e: THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conservative href="1072-6American Cowboy href="6035-6American Handgunner href="3343-6American Heritage href="2498-4American Heritage of Invention & Technology BECOMES THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conse...

Masking the sent from field with a seperate domain?
We have Exchange 2k3 and AD setup. We use the pop3 connector to download email from various seperately hosted domains and route the email to the correct client. Our users have noticed that any email they send shows the recipient the local domain in the sent from field. We need to mask it to say that it come from a different domain... for example one of the domains that we use to download email from using the pop3 connector. Is this possible? Any help would be greatly appreciated. Jackie Jackie wrote: > We have Exchange 2k3 and AD setup. We use the pop3 connector to > down...

Seperators
Hi I hope that someone can help? I have been sent a work book where several columns have data seperations between words as follows: "UK Pounds" The data appears to have a square box in between the "UK" and the "Pounds" Please can you advise a quick way to change the format in all columns in the worksheet Saved from a previous post: Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a...

Random Number Generator #2
I need to create a random number generator in Excel 2000 to fill one column and about 3000 rows. How do I go about doing this? To generate a random whole number between 1 and 3000, you can use =int(rand()*(3000-1)+1) binder Wrote: > I need to create a random number generator in Excel 2000 to fill on > column > and about 3000 rows. How do I go about doing this -- BenjieLo ----------------------------------------------------------------------- BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101 View this thread: http://www.excelforum.co...

Plain Text Direction
I have purchased Notebook with Vista from Dubai. My language set is as English and Keyboard US ( Vista Home Regional Langague setting ). In office 2007, I have set langague as ENGLISH USA. In microsoft Outlook 2007, I am expereincing unique problem. All incoming mails in PLAIN TEXT which include emails, read receipt and delivery reports are appearing in Right to Left Direction. Repeat in PLAIN TEXT FORMAT. There is no problem in HTML and Rich Text Messages. I want to set text direction as LEFT TO RIGHT as defualt for PLAIN TEXT MSGES. In Option, I could not find tab for LEFT TO RI...

How to Print PO Number and Check Number to GL Trial Balance Detail
Hi, Can anyone help me to print the PO Number from Purchase Order Entry and Check Number from Payables transaction Entry to GL Trial Balance Detail report. I've searched some knowledgebase but it doesn't work. can you make me that report or can you give me step by step procedure on how to print po number and check number. The code i've already check are listed below: Note: it doesn't work! 1. 863689 2. 855515 3. 850888 Pls hope for your response -- Jeremy Ayaay Technical Supervisor STRC You should insert the orginating document number from the Year to Date Table this ...

have 3 pieces of data in a cell seperated by a comma. How can I isolate the middle piece of data?
I've imported a large file of data that is basically three columns of data each seperated by a comma. (I could not figure out how to import them into a new spreadsheet so that each piece of data was automatically put into it's own column - but that is a different question) How can I do a "search replace" that will delete all of the data up to the firts comma? Then I would want to delete the data after the second comma in order to isolate the middle portion of data? Here is an example of the data: "10302003-12445","17.99","xyz21" Is there a be...

display number as number name
I want to get the number name of the corresponding number i enter. Hi see: http://www.xldynamic.com/source/xld.xlFAQ0004.html "Kamala Kannan" wrote: > I want to get the number name of the corresponding number i enter. ...

formula to BOLD partial number
Helo, MY experience with your group is awesome. Can you help me with this one, I have time data "06:09:23" is there a formula where I can indicate the first two caracters ("06") to be bold. Manually to do this could be quite a hassle for a couple of hundred cells. Thank you smile One simple way Sub boldleft2() For Each c In Selection c.Characters(1, 2).Font.Bold = True Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "israel" <israel@discussions.microsoft.com> wrote in message news:1C76F7C5-01F4-4033-8...

Seperate words in a field
I have data that has information in a field that I need to separate. Example: Jimmy Ray (2004) (director) (producer) Jenny Rich (2000) (Actress) From this example I need to separate the items in parentheses from the name and enter them all in different columns. Is there a query or other possible way to do this? Thanks for any help given. As I see it this would be a 2-step process.. First you would separate all words: Divide text across cells Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one...

text : Frequency
Hi, I need to know how many time I have a stringA StringB and a StringC in a column. I think frequency is only for numbers. How can I do it with Strings? So With the frequecies of each 3 strings,I will be able to build a flowchart. Thanks, J Jack use COUNTIF For example: =COUNTIF(A1:A200,B1) for a range in column A; adjust to suit or: =COUNTIF(A:A,B1) for all of column A Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:2a8f01c3fcad$c4a70dd0$a001280a@phx.gbl... > Hi, > > I nee...

Default Text
How can I change the text default from "plain text" to "HTML"? I know it is probably easy, but I can't find the option anywhere. Any help is welcome. You don't say what version of Outlook you are using, but in 2003 (and XP, if I remember correctly), at the Inbox menu, select Tools, Options, Mail Format. Judy Freed Systems Development UNC Charlotte "Nancy" <photoconcepts@charter.net> wrote in message news:960801c4338e$4b5dfd40$a501280a@phx.gbl... > How can I change the text default from "plain text" > to "HTML"? I know i...

Serial Number
I need to delet hundereds of old sold serial number. Is there way I can get script ? All the serials are under one item. -- Thank You Harjit SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 Tel : 209-729-1410 vendaliajat@yahoo.com Hi Gill - Of the serial numbers you need to delete, how many line items are they attached to (i.e. hundreds of serial numbers under 1 item, 2 items, or more)??? There are some easy scripts to remove these, but it will all depend on: 1) What side is deleting (HQ or SO)? 1a) If HQ - will you also delete from SO? 2) Are the serial...

How do I move text to beside (rather than after) a photo?
I have MS Word 2007. When I place a photo or text box onto a page, I can't then get text to enter beside it. The cursor will only go to the side just above or below the photo. How do I enter text alongside the picture? Change the text wrapping on the photo or text box to Square. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Mrs Mainster" <Mrs Mainster@discussions.microsoft.com> wrote in message news:5C43DF85-E305-4DEC-8F8A-3BF1D544FF62@microsoft.com... >I have MS Word 2007. > When...

Delete an unused check number
Is there a way to delete an unused check number not entered into the system? We used a check for a manual payment, but ended up voiding the check as it was not needed. Since the check was never printed, this check number does not appear in the system so I can't void it. But the physical check itself is obviously not usable now. Thanks for any help. Melissa, The only thing I can think of is entering a manual payment for $0 to use up this check number. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "melissa" <melissa@discussions.microsoft.com> wrote in ...

How to draw a vertical text properly?
Could you give me a favor? How to draw a text vertically and make it looks good? I was trying to change a lfEscapement of LOGFONT, but then it looks sharp. lfQuality flag doesn't make a sense. Tnaks. ...

Repeat row numbers
Excel 2003 I need to number rows on a large spreadsheet from 1 - 27, and repeat 1 - 27, etc to the end. I'd rather not copy and paste! TIA Carole O Put 1 in a1, and this formula in A2, and copy down: =if(a1=27,1,a1+1) Regards, Fred "Carole O" <CaroleO@discussions.microsoft.com> wrote in message news:2A01BD31-CEE3-45D9-B5B5-D0E060869E49@microsoft.com... > Excel 2003 > I need to number rows on a large spreadsheet from 1 - 27, and repeat 1 - > 27, > etc to the end. I'd rather not copy and paste! > > TIA > > Carole O...

Do not count minus-numbers
I have some number I would like to SUM, but I dont want excel to calculate the minus-number. Here is what I have - A1 10 B1 20 C1 30 D1 40 E1 -1000 F1 0 G1 0 I do it like this SUM(A1:G1) But I want it to say SUM(10+20+30+40+0+0) How can I do it like that ? May be =sumif(A1:G1,">0") "SpookiePower" <boxjunk2600@gmail.com> wrote in message news:4ef118e1-4171-4509-a5dd-eaf1fd0a91e6@v19g2000yqn.googlegroups.com... >I have some number I would like to SUM, but I dont want excel to > calculate the minus-number. > > Here is what I have - > > A1 10 ...

using text box as query criteria
I have a form with a subform. My main form has a text box wich is used in the query expression of my subform. In the KeyUp event of the text box I refresh the forms. This works to filter my subform each time a new character is entered into the text box. My problem is every time a key is pressed the refresh selects the entire contents of the text box. each new letter you type replaces the last making it impossible to type an entire word. Is there and easy work around. Is there a way to mimic the end key behavior in code? TIA, Tim On Fri, 23 Feb 2007 16:11:05 -0800, "Tim ...

macro based on text or number
I want to do an if statement within a macro that will copy and paste to another cell, a field that is text. Anything numerical should be skipped. How do I do this? Here is one way. Set rng = Sheets("Sheet1).Range("A1") If Not IsNumeric(rng) Then rng.Copy Sheets("Sheet2").Range("A2") End If "JOSEPH WEBER" <JOSEPHWEBER@discussions.microsoft.com> wrote in message news:A5D52B8A-7923-416B-9C20-3F18A7594374@microsoft.com... >I want to do an if statement within a macro that will copy and paste to > another cell, a field...

How to HR user to modify GAL user phone number and address details.
Hello, I need to allow a HR user to modify GAL user phone number and address details. What is the cleanest way to allow these permissions only? Exchange 2003, outlook 2003 Quest Active Roles Server <vyaw2003@gmail.com> wrote in message news:1170223431.607031.241980@k78g2000cwa.googlegroups.com... > Hello, > I need to allow a HR user to modify GAL user phone number and address > details. > What is the cleanest way to allow these permissions only? > Exchange 2003, outlook 2003 > **I need to allow a HR user to modify the GAL through Outlook 2003 cant i just delega...

Master Transaction Number & Trx Source Number
In SOP what this numbers mean Master Transaction Number Trx Source Number What is the initial tables for this number. Thanks a lot. V, This really is a developer question that may be better posted/answered on the mbspartner.developer ng. That is for GP and SBF developer questions. Matt "Vitali V" <vitvov@dynamo-ny.com> wrote in message news:uIVuOK1sFHA.4028@TK2MSFTNGP10.phx.gbl... > In SOP what this numbers mean > Master Transaction Number > Trx Source Number > > What is the initial tables for this number. > > > Thanks a lot. > > Tha...

Running Total of Random Number
Hi, I am trying to create a probablity simulation for a high school lesson. It involves rolling 1/2 dice using the randbetween function to create the die value. I want to keep a running total of the outcomes 1-6 in a cell for each which is linked to a chart. Is this possible? Do you mean a running total of all the values from the dice, if so do tools>options>calculation and check iteration and change from 100 to 1, assume randbetween formula is in A1 and you want the running total in B1, in B1 put =A1+B1, press F9 to calculate Regards, Peo Sjoblom "Mr H" wrote: &...

Adding a Mobile Phone Number field to a Case View
Hi, Is it possible to add the customers mobile phone number field to a Case View ? Thanks Marc Harrington have you tried adding the attribute to the entity? "Marc Harrington" wrote: > > Hi, > > Is it possible to add the customers mobile phone number field to a Case View ? > > Thanks > Marc Harrington > Thanks for the reply Ian We tried adding a mobile phone attribute to the case entity with no success. There is a system relationship between the case and contact entities but we cannot create a relationship between the case entity and the conta...

transposing numbers.
I wonder if anyone can help. I have a list of numbers that are sorted in numerical order. I typed them from up to botom. I would like to display them horizontally instead. For example, they look like this: 1 2 3 4 5 I would like them to look like this: 1 2 3 4 5 Can someone help? Thanks in advance "Rimma" <anonymous@discussions.microsoft.com> wrote in message news:00a001c3ba7e$6987f7f0$a101280a@phx.gbl... > I wonder if anyone can help. > I have a list of numbers that are sorted in numerical > order. I typed them from up to botom. I would like to > disp...