How to split a cell into 3 columns

  • Thread starter Thread starter grim72
  • Start date Start date
G

grim72

I have a table of data with product codes
I want to split the code into 3 sections as below:
Full code: 17896Z

Split into:
1
7896
Z

I know how to get the 1 and the Z using =LEFT(A1,1) or =RIGHT(A1,1)
but wasn't sure how to get the middle section?
 
I have a table of data with product codes
I want to split the code into 3 sections as below:
Full code: 17896Z

Split into:
1
7896
Z

I know how to get the 1 and the Z using =LEFT(A1,1) or =RIGHT(A1,1)
but wasn't sure how to get the middle section?
Click to expand...

If your sizes of the codes are consistent, use MID(A1,2,4).
 
Hi Grim72,

The below formula will work on any number of characters and bring the
first letter, last letter and the remaining letters by ignoring the
first and last letters.

Text Results:-
=LEFT(TRIM(A1),1)
=MID(A1,2,LEN(TRIM(A1))-2)
=RIGHT(TRIM(A1),1)

Real Numbers:-
=VALUE(LEFT(TRIM(A1),1))
=VALUE(MID(A1,2,LEN(TRIM(A1))-2))

Real Numbers values are always stay in right side and text number
values will stay in left side. Use Value function in front of the
formula to convert the text value to real value.

Hope it's clear to you!!!
 
Back
Top