Extract text

Hi All

I have some text as below:

10-1
9-12
10-10

I need to be able to extract the numbers to the left of the dash and insert 
them in a separate column and also to extract the numbers to the right of the 
dash and place them in a separate column.

Column
A       B
10     1
9       12
10     10

Any pointers would be much appreciated.

Kind Regards

Celticshadow
0
9/15/2009 3:58:01 PM
excel 39879 articles. 2 followers. Follow

5 Replies
577 Views

Similar Articles

[PageSpeed] 1

Try using Data>Text to columns

Make sure there is an empty column to the immediate right of your data.

Select your data
Goto the menu Data>Text to Columns
Delimited>Next
Check Other and enter a dash in the little box
Finish

-- 
Biff
Microsoft Excel MVP


"Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message 
news:CE3C6183-99F9-49FA-A5D8-53D51847DD86@microsoft.com...
> Hi All
>
> I have some text as below:
>
> 10-1
> 9-12
> 10-10
>
> I need to be able to extract the numbers to the left of the dash and 
> insert
> them in a separate column and also to extract the numbers to the right of 
> the
> dash and place them in a separate column.
>
> Column
> A       B
> 10     1
> 9       12
> 10     10
>
> Any pointers would be much appreciated.
>
> Kind Regards
>
> Celticshadow 


0
biffinpitt (3172)
9/15/2009 4:14:41 PM
Hi

That does work but it is not quite what I am after. The column with the data 
in could run to 40 rows and thus I require to run this as a formula all the 
way down an adjacent column/s as below.

Column
A               B      D
10-1          10     1
9-12           9     12
10-10         10    10
8- 7            8      7

Many thanks 

Celticshadow

"T. Valko" wrote:

> Try using Data>Text to columns
> 
> Make sure there is an empty column to the immediate right of your data.
> 
> Select your data
> Goto the menu Data>Text to Columns
> Delimited>Next
> Check Other and enter a dash in the little box
> Finish
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message 
> news:CE3C6183-99F9-49FA-A5D8-53D51847DD86@microsoft.com...
> > Hi All
> >
> > I have some text as below:
> >
> > 10-1
> > 9-12
> > 10-10
> >
> > I need to be able to extract the numbers to the left of the dash and 
> > insert
> > them in a separate column and also to extract the numbers to the right of 
> > the
> > dash and place them in a separate column.
> >
> > Column
> > A       B
> > 10     1
> > 9       12
> > 10     10
> >
> > Any pointers would be much appreciated.
> >
> > Kind Regards
> >
> > Celticshadow 
> 
> 
> 
0
9/15/2009 6:36:01 PM
OK, try these:

For column B...

=--LEFT(A1,FIND("-",A1)-1)

For column D...

=--MID(A1,FIND("-",A1)+1,20)

Note that these formulas will return the values as NUMBERS. If any of your 
entries in column A have leading zeros, 001-05, then these leading zeros 
will be stripped off. If that's the case and you need to retain those 
leading zeros then just remove the double dashes from each formula.

-- 
Biff
Microsoft Excel MVP


"Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message 
news:67ED8358-5DAC-49DD-AF28-7FE7E19F20F4@microsoft.com...
> Hi
>
> That does work but it is not quite what I am after. The column with the 
> data
> in could run to 40 rows and thus I require to run this as a formula all 
> the
> way down an adjacent column/s as below.
>
> Column
> A               B      D
> 10-1          10     1
> 9-12           9     12
> 10-10         10    10
> 8- 7            8      7
>
> Many thanks
>
> Celticshadow
>
> "T. Valko" wrote:
>
>> Try using Data>Text to columns
>>
>> Make sure there is an empty column to the immediate right of your data.
>>
>> Select your data
>> Goto the menu Data>Text to Columns
>> Delimited>Next
>> Check Other and enter a dash in the little box
>> Finish
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message
>> news:CE3C6183-99F9-49FA-A5D8-53D51847DD86@microsoft.com...
>> > Hi All
>> >
>> > I have some text as below:
>> >
>> > 10-1
>> > 9-12
>> > 10-10
>> >
>> > I need to be able to extract the numbers to the left of the dash and
>> > insert
>> > them in a separate column and also to extract the numbers to the right 
>> > of
>> > the
>> > dash and place them in a separate column.
>> >
>> > Column
>> > A       B
>> > 10     1
>> > 9       12
>> > 10     10
>> >
>> > Any pointers would be much appreciated.
>> >
>> > Kind Regards
>> >
>> > Celticshadow
>>
>>
>> 


0
biffinpitt (3172)
9/15/2009 6:51:18 PM
Hi again

What a swift response. That works an absolute treat. Many thanks for your 
help it really is appreciated, a credit to the forum.

Thanks again

Celticshadow

"T. Valko" wrote:

> OK, try these:
> 
> For column B...
> 
> =--LEFT(A1,FIND("-",A1)-1)
> 
> For column D...
> 
> =--MID(A1,FIND("-",A1)+1,20)
> 
> Note that these formulas will return the values as NUMBERS. If any of your 
> entries in column A have leading zeros, 001-05, then these leading zeros 
> will be stripped off. If that's the case and you need to retain those 
> leading zeros then just remove the double dashes from each formula.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message 
> news:67ED8358-5DAC-49DD-AF28-7FE7E19F20F4@microsoft.com...
> > Hi
> >
> > That does work but it is not quite what I am after. The column with the 
> > data
> > in could run to 40 rows and thus I require to run this as a formula all 
> > the
> > way down an adjacent column/s as below.
> >
> > Column
> > A               B      D
> > 10-1          10     1
> > 9-12           9     12
> > 10-10         10    10
> > 8- 7            8      7
> >
> > Many thanks
> >
> > Celticshadow
> >
> > "T. Valko" wrote:
> >
> >> Try using Data>Text to columns
> >>
> >> Make sure there is an empty column to the immediate right of your data.
> >>
> >> Select your data
> >> Goto the menu Data>Text to Columns
> >> Delimited>Next
> >> Check Other and enter a dash in the little box
> >> Finish
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message
> >> news:CE3C6183-99F9-49FA-A5D8-53D51847DD86@microsoft.com...
> >> > Hi All
> >> >
> >> > I have some text as below:
> >> >
> >> > 10-1
> >> > 9-12
> >> > 10-10
> >> >
> >> > I need to be able to extract the numbers to the left of the dash and
> >> > insert
> >> > them in a separate column and also to extract the numbers to the right 
> >> > of
> >> > the
> >> > dash and place them in a separate column.
> >> >
> >> > Column
> >> > A       B
> >> > 10     1
> >> > 9       12
> >> > 10     10
> >> >
> >> > Any pointers would be much appreciated.
> >> >
> >> > Kind Regards
> >> >
> >> > Celticshadow
> >>
> >>
> >> 
> 
> 
> 
0
9/15/2009 7:05:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message 
news:5957EA63-C19D-4DD1-B755-297D47AA4CBD@microsoft.com...
> Hi again
>
> What a swift response. That works an absolute treat. Many thanks for your
> help it really is appreciated, a credit to the forum.
>
> Thanks again
>
> Celticshadow
>
> "T. Valko" wrote:
>
>> OK, try these:
>>
>> For column B...
>>
>> =--LEFT(A1,FIND("-",A1)-1)
>>
>> For column D...
>>
>> =--MID(A1,FIND("-",A1)+1,20)
>>
>> Note that these formulas will return the values as NUMBERS. If any of 
>> your
>> entries in column A have leading zeros, 001-05, then these leading zeros
>> will be stripped off. If that's the case and you need to retain those
>> leading zeros then just remove the double dashes from each formula.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in message
>> news:67ED8358-5DAC-49DD-AF28-7FE7E19F20F4@microsoft.com...
>> > Hi
>> >
>> > That does work but it is not quite what I am after. The column with the
>> > data
>> > in could run to 40 rows and thus I require to run this as a formula all
>> > the
>> > way down an adjacent column/s as below.
>> >
>> > Column
>> > A               B      D
>> > 10-1          10     1
>> > 9-12           9     12
>> > 10-10         10    10
>> > 8- 7            8      7
>> >
>> > Many thanks
>> >
>> > Celticshadow
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try using Data>Text to columns
>> >>
>> >> Make sure there is an empty column to the immediate right of your 
>> >> data.
>> >>
>> >> Select your data
>> >> Goto the menu Data>Text to Columns
>> >> Delimited>Next
>> >> Check Other and enter a dash in the little box
>> >> Finish
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Celticshadow" <Celticshadow@discussions.microsoft.com> wrote in 
>> >> message
>> >> news:CE3C6183-99F9-49FA-A5D8-53D51847DD86@microsoft.com...
>> >> > Hi All
>> >> >
>> >> > I have some text as below:
>> >> >
>> >> > 10-1
>> >> > 9-12
>> >> > 10-10
>> >> >
>> >> > I need to be able to extract the numbers to the left of the dash and
>> >> > insert
>> >> > them in a separate column and also to extract the numbers to the 
>> >> > right
>> >> > of
>> >> > the
>> >> > dash and place them in a separate column.
>> >> >
>> >> > Column
>> >> > A       B
>> >> > 10     1
>> >> > 9       12
>> >> > 10     10
>> >> >
>> >> > Any pointers would be much appreciated.
>> >> >
>> >> > Kind Regards
>> >> >
>> >> > Celticshadow
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
9/15/2009 8:29:27 PM
Reply:

Similar Artilces:

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

I am trying to change the text size of the folder list in Outlook
not the preview pane, the actual folder list in the Inbox Window. It's so tiny. "Bayoubelle" <Bayoubelle@discussions.microsoft.com> wrote in message news:D431653F-FA94-4AD2-9668-380846EE6CB2@microsoft.com... > not the preview pane, the actual folder list in the Inbox Window. It's so > tiny. Always state your Outlook version. Click View>Arrange By>Current View>Customize Current View>Other Settings. Change the Column Font and Row Font settings to suit. If you're using Outlook 2007, then it's View>Current View>... I...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Plain text Outlook on Web
Does anybody know how to send email (preferable *all* email) as plain text, using the Outlook On Web browser interface? Many thanks in advance. -- Ed. Ed Weatherup <invalid@invalid.invalid> wrote: > Does anybody know how to send email (preferable *all* email) as plain > text, using the Outlook On Web browser interface? As far as I can tell, Outlook Web Access can't be made to send plain text format. -- Brian Tillman Brian Tillman wrote: > Ed Weatherup <invalid@invalid.invalid> wrote: > >> Does anybody know how to send email (preferable *all* ema...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Text Wrap
I have text wrap on a cell with lots of text and I use "Alt Enter" to create new lines within the cell. The last two entries in the cell are not wrapping though the first four entries within the cell wrap just fine. Is there a way to get the last two entries within the cell to text wrap? Thanks! Is the rowheight big enough? And if those last two entries are really long, then maybe a few more alt-enters to break them up???? Craig wrote: > > I have text wrap on a cell with lots of text and I > use "Alt Enter" to create new lines within the cell. The > l...

Repeating a formula in text
I'm concatenating two columns in an excel data file. The formula works, but won't replicate in the column to subsequent rows, all it will do is copy. I'm tearing my hair out - I've done it before but all of the sudden it won't work. Can you help? 1) What happens if you hit [F9] ? If this recalculates the correct results - then your Calculation option is set to "Manual". 2) Did you, by mistake, fix the References of the concatenated cells with $ [like: $A$1] ? Micky "Lyn" wrote: > I'm concatenating two columns in an ex...

how to draw text vertically?
for example, some software like Microsoft Word, when docking a toolbar to left or right, the text on button will be drawn vertically. In article <eShIl7FZGHA.3704@TK2MSFTNGP03.phx.gbl>, Bill Gates says... > for example, some software like Microsoft Word, when docking a toolbar to > left or right, the text on button will be drawn vertically. Basically you specify the angle in lfEscapement of the LOGFONT structure. Then call CreateFontIndirect with this structure and you'll get a rotated font of type HFONT. Use SelectObject to select the font and then you can use TextO...

Text running
Hello I don't know if there is a code that keeps a word ( for example FOOTBALL) running from left to the right of the cell. I appriciate even a small help. Thanks in advance Where would you like it to run to? English and most other languages run left to right. Middle East countries usually run right to left. There are various methods of text alignment in cell formatting>alignment. Gord Dibben MS Excel MVP On Thu, 15 Apr 2010 09:39:01 -0700, MAX <MAX@discussions.microsoft.com> wrote: >Hello >I don't know if there is a code that keeps a wor...

How can I view word wrapped text in cells that have been merged?
Formtat -> Row -> Autofit is not working Manually resize the rowheight. (merged cells don't react to autofitting of rows) Xcelsearcher wrote: > > Formtat -> Row -> Autofit is not working -- Dave Peterson ec35720@netscape.com ...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Text Printer Fonts
How can I allow my Generic Text printer to access more than the default CPI fonts? Can I make my laser printer fonts available for my Generic Text printer? You can't! If you want your laser printer fonts available your laser printer must be the active printer driver. What *exactly* are you trying to achieve? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<...

How to change text string?
I have used the CONCATENATE function and obtained the result I wanted but not in the form I need. My results are all joined into one text string (A,B,C,D) however they need to be one beneath the other in the same cell: A B C D Thank you Hi! Try something like this: Format the cell to wrap text: =C8&CHAR(10)&C9&CHAR(10)&C10&CHAR(10) In the above, I'm concatenating cells C8, C9 and C10 Biff "Dajana" <Dajana@discussions.microsoft.com> wrote in message news:58C16654-4112-4B8C-A9F8-B104B5AFFBCE@microsoft.com... >I have used the CONCATENATE funct...

Drag & drop text from other application
Hi, I`m writing an app that need to drag&drop a plain text (it`s actually the html text but converted to plain) into MS Word (2007 for now). Also, user should be able to drop text to a html editor. So on drag&drop I`ve registered two d&d formats (html and plain text) in the d&d clipboard. I can drop the text into a html editor but Word doesn`t recognize my plain text and do nothing when I drop the text. If I register only the plain text then all fine. That`s how I register d&d formats (C#): dragContent.SetText(<html text>, TextDataFormat.Html); dragContent...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

MID formula not extracting what I want.
I'm trying to extract the date from a cell using the MID formula and all I'm getting is some weird number that does not seem to make sense. I have tried formatting the target cell but still no change. Any clues about it? How can I do this? 1/13/2006 12:16:29 AM =MID(E5,1,9) 38730.011 -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860 View this thread: http://www.excelforum.com/showthread.php?threadid=501062 Hi That's because Excel stores dates as...

Text problems
Using a newsletter created by Publisher 2000, opening the newsletter in Publisher 2002 and saving as a web page some of the text lines are over writen by the next line and the text that fills a column in the newsletter leaves a lot of white space at the bottom of the column. Is there any way to fix this problem or do I continue to use Publisher 2000 and give up on Publisher 2002? Thanks for any help any one can give. Bill ...

How do I turn off the text to speech facility in Exccel?
I am trying to turn off the text to speech facility in Excel. Please help. On Sat, 4 Jun 2005 01:29:26 -0700, "David Boas" <David Boas@discussions.microsoft.com> wrote in microsoft.public.excel.misc: >I am trying to turn off the text to speech facility in Excel. Please help. On the Speech toolbar, click Speak On Enter, the rightmost button. See also Help: "Turn off speech playback" where it says: Turn off speech playback Do one of the following: To turn off all speech playback, click Stop Speaking on the Text To Speech toolbar. To turn on or...

Automatically have the text changed when the value changes
I need to know how to get the text automatically changed when the selected value changes Text is where? Selected value is where? Gord Dibben Excel MVP On Sat, 26 Nov 2005 09:31:04 -0800, "abhijeet" <abhijeet@discussions.microsoft.com> wrote: >I need to know how to get the text automatically changed when the selected >value changes ...