How to split number and text in excel into different columns [Solved]

Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
- - Latest reply: ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Jan 29, 2019 at 04:53 PM
Good day

Please assist with a formula what I can use to split text and number from one column into two columns.

This is what I have
COLUMN A
ABC678
J89K12
KKPNIT
5PB7Y1

Now I need to split it into 2 columns
TEXT NUMBER
ABC 678
JK 8912
KKPNIT 0
PBY 571

Thank you in advance
Reinette
See more 

3 replies

Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
0
Thank you
I noticed the two columns did not display very well
COLUMN B
ABC
JK
KKPNIT
PBY

COLUMN C
678
8912
0
571
You cant without a delimiter. That or a set characyer length. If all text and number combinitions were three characters then tes, but in your example, you give mutliple formattingx with no deliminater. You can check if it is text.

So tou could evaluate with thisx but im not going to spend my time writing your homework code.

Follow this logic
Evaluate the cell for length.
Build a llop that checks each character location and determin if it is text or not with istext ()
upon the return value being true or false you have your text or number length as long as the character combinition does alter BACK to either text or integer!
Respond to Gerberreinette
Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
0
Thank you
Hi thank you very much. I am not looking for code but for a Excel formula. I tried everything but I am not winning. For assets we have different barcodes and that is why there is no standard format for it.

Thank you for the response
Ok so who creayes the sheets and have them olace a delimiter in the cells. Did you notbrwad my explanation? Unless you have a standardized feild length or a delimiter, it is not possible without a custom function that I am not convinced that this isnt homework of some type. Have the creator of the sheet seperate the cells or place a delimiter!
Respond to Gerberreinette
Posts
11
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 18, 2019
0
Thank you
Hi I manage to get a formula thanks.

B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

and pulled down to other rows in "B" column

C1=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

and pulled down to other rows in "C" column
ac3mark
Posts
13036
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1465 -
WOW, where did you cut and paste that from?
Respond to Gerberreinette