Is there a formula that will separate numbers from text from a cell, I know how to use Left, Right and Mid formulas but they will not work

[jcaptchaos2]

Here is a couple of examples and the data changes all the time so I can't go in and change the number of characters left or right.

Here are some examples
5452121-699 Red and Blue - Need 545211-699 in one cell and Red and Blue in another
5452121 Red and Blue - Need 545211 in one cell and Red and Blue in another
2881797-02 Red and Blue - Need 2881797-02 in one cell and Red and Blue in another.

Any help would be appreciated.

 

[Sufiyan97]

Try

Book8
	A	B	C
1
2	5452121-699 Red and Blue	5452121-699	Red and Blue
3	5452121 Red and Blue	5452121	Red and Blue
4	2881797-02 Red and Blue	2881797-02	Red and Blue
5
6
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,SEARCH(" ",A2)-1)
C2:C4	C2	=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH(" ",A2)+1,99),"-",REPT(" ",99)),99))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option is

Fluff.xlsm
	A	B	C
1
2	5452121-699 Red and Blue	5452121-699	Red and Blue
3	5452121 Red and Blue	5452121	Red and Blue
4	2881797-02 Red and Blue	2881797-02	Red and Blue
Main

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTBEFORE(A2," ",1)
C2:C4	C2	=TEXTAFTER(A2," ",1)

 

[jcaptchaos2]

Excellent Thank You so Much!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jcaptchaos2]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option is

Fluff.xlsm
	A	B	C
1
2	5452121-699 Red and Blue	5452121-699	Red and Blue
3	5452121 Red and Blue	5452121	Red and Blue
4	2881797-02 Red and Blue	2881797-02	Red and Blue
Main

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTBEFORE(A2," ",1)
C2:C4	C2	=TEXTAFTER(A2," ",1)

Will do Thanks for the reminder