How to split number and text in excel into different columns
Solved/Closed
Gerberreinette
Posts
14
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 31, 2023
-
Jan 28, 2019 at 01:27 AM
Blocked Profile - Jan 29, 2019 at 04:53 PM
Blocked Profile - Jan 29, 2019 at 04:53 PM
Related:
- How to split number and text in excel into different columns
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- How to change author in excel - Guide
- How to change date format in excel from text to dd/mm/yyyy - Guide
- Excel free download - Download - Spreadsheets
3 responses
Gerberreinette
Posts
14
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 31, 2023
2
Jan 29, 2019 at 01:07 AM
Jan 29, 2019 at 01:07 AM
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
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
Gerberreinette
Posts
14
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 31, 2023
2
Jan 28, 2019 at 01:29 AM
Jan 28, 2019 at 01:29 AM
I noticed the two columns did not display very well
COLUMN B
ABC
JK
KKPNIT
PBY
COLUMN C
678
8912
0
571
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!
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!
Gerberreinette
Posts
14
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 31, 2023
2
Updated on Jan 28, 2019 at 08:05 AM
Updated on Jan 28, 2019 at 08:05 AM
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
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!
Jan 29, 2019 at 04:53 PM