Splitting Column Cells into Two Columns

Referring to the screen shot below

Formula in B2: =VALUE(LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1))

Formula in C2: =MID(A2,LEN(B2)+1,255)

If desired, you can remove the formulas later by selecting the columns containing the formulas and Copy -> Paste Special >- Values to remove the formulas.

Also, if all data is a single unit type and you are wanting to use the values but still display the kg with the value after using the formulas in column B, you can use Custom Number Format.

The Custom number format is as follows.

0"kg" (That is zero, double quote, kg and double quote again.

Hi Kailyn. I am an Excel user like you.

Excel will automate this with Flash Fill.

Enter the breakdown you want for the first row of the data as below..

Then highlight the range that you want to fill in. In the screenshot this would be B2:B5. Then click on Flash Fill on the Data ribbon in the Data Tools section.

Excel will fill in the corresponding data.

Then repeat for the next column highlighting the range (C2:C5 this time) and click Flash Fill.

@Rich-M

Absolutely magic. My original reason for joining the forum was to pick up on stuff that I didn't know about.

@Kailyn Kilroe

You might be interested in the following couple of Microsoft links with further information on FlashFill.

Enable Flash Fill in Excel - Microsoft Support

Using Flash Fill in Excel - Microsoft Support

If you have seperators in your string, it may cause issues with your excellent solution.

(the formula in B7, and another formula in C7)

I would use a simplier worksheet function like in E2?

Hi,

In cell C2, enter this formula

=TEXTSPLIT(A2,VSTACK(".",SEQUENCE(10,,0)),,TRUE)

In cell B2, enter this formula

=1*(LEFT(A2:A4,SEARCH(C2:C4,A2:A4)-1))

Hope this helps.