Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default How to split number and text string to separate cells?

I have in cells A1:A1000....a combination of number and text, ex.."123 abc abc"

I wanted to split into 2 separate cells...I've done the number part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 703
Default How to split number and text string to separate cells?

Hi

Try this in C1:

=RIGHT(A1,LEN(A1)-LEN(B1))

Regards,
Per

On 29 Sep., 02:07, Lai704 wrote:
I have in cells A1:A1000....a combination of number and text, ex.."123 abc abc"

I wanted to split into 2 separate cells...I've done the number part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default How to split number and text string to separate cells?

Your formula is not entirely correct, you have included a trailing space

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


will give 123 as text


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

as number


In C1 you can simply use


=TRIM(SUBSTITUTE(A1,B1,""))


--


Regards,


Peo Sjoblom





"Lai704" wrote in message
...
I have in cells A1:A1000....a combination of number and text, ex.."123 abc
abc"

I wanted to split into 2 separate cells...I've done the number
part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default How to split number and text string to separate cells?

hi
try this. it works for your example

=MID(B3,FIND(" ",B3),200)

regards
FSt1

"Lai704" wrote:

I have in cells A1:A1000....a combination of number and text, ex.."123 abc abc"

I wanted to split into 2 separate cells...I've done the number part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default How to split number and text string to separate cells?

Hi,

If you are going to use MID you need to modify the previous example:

=MID(A1,FIND(" ",A1)+1,200)

notice this matches the issue with LEFT

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

and if you want to use right by itself

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

We don't know enough about your data, but the tool of choice for splitting
data is Data, Text to Columns.

--
Thanks,
Shane Devenshire


"FSt1" wrote:

hi
try this. it works for your example

=MID(B3,FIND(" ",B3),200)

regards
FSt1

"Lai704" wrote:

I have in cells A1:A1000....a combination of number and text, ex.."123 abc abc"

I wanted to split into 2 separate cells...I've done the number part...using
"=LEFT(A1,FIND(" ",A1))" to cell B1

but I can't seem to figure out how to split out "abc abc" to C1?

Any help is appreciated.

Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Split a number from one cell to separate cells for each Rob Excel Discussion (Misc queries) 11 June 23rd 08 10:24 PM
separate a text cell to many number cells Khoshravan[_2_] Excel Worksheet Functions 1 July 19th 07 11:22 PM
Parsing a text string into separate cells Bobalew Excel Worksheet Functions 1 June 6th 07 09:16 PM
split text into separate cells jtaiariol Excel Worksheet Functions 7 May 7th 07 09:32 PM
Split date in cell into 2 separate cells Jambruins Excel Discussion (Misc queries) 1 August 9th 06 06:16 PM


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"