3

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number.

QA32 Product One (Large) 23
AZ63 Product Two 96
PR65 Product Three 149

How do I split each field into a column?

I tried the "split text into columns" on excel using the delimited option and separating by spaces but the fact that some product names had more that one word caused the some numbers to be in the wrong column. E.g.,

Code     Name       Number       
QA32    Product      One       (Large)    23
AZ63    Product      Two         96
PR65    Product      Three       149

This is the desired result:

Code        Name                Number
QA32    Product One (Large)       23
AZ63    Product Two               96
PR65    Product Three             149

Help is appreciated!

1

2 Answers 2

2

Use FIND-function to determine the position of your first and last space. After that, use theMID-function to split your data into three parts. If you know the position of your spaces, the splitting is easy. Finding the first space is also trivial since FIND goes from left to right. Unfortunatly, there is no way to tell Excel to search from right to left.

So the difficult part is, to determine the last space! Here we use an array formula.
To enter an array formula, you paste the formula into a cell and don't hit Enter.
Instead you press Ctrl+Shift+Enter.

{=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A))}

If done successfully, Excel will append a starting and ending curly bracket.


Insert all formulas at the given cells and use autofill down

A2: your data goes here
B2:=FIND(" ",A2,1)
C2:=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A)) <-array formula!
D2:=MID(A2,1,B2-1)
E2:=MID(A2,B2+1,C2-B2-1)
F2:=MID(A2,C2+1,LEN(A2))

Result screen
enter image description here

1
  • =MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A)) <-array formula! is a cool formula
    – Learner
    Mar 17, 2016 at 11:54
0

If a suitable algorithm is to return:

  • Code = The first word
  • Product = Everything between the first and last words
  • Number = The last word

Then the following formulas should work.

Assume your data is in A1:

Code:     =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
Product:  =TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),100,LEN(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)))-198))
Number:   =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .