Share via

Splitting Column Cells into Two Columns

Anonymous
2024-05-05T23:52:09+00:00

I am looking to split my cells from one column into two separate columns. I have data with an amount and a unit of measure (ex. 5kg). I am looking to separate that data into two columns, so it would be the amount in one column and the unit of measure in another tab (so 5 would be in one column and the column to the right would be kg). I have tried using the text to columns, but need more description on this. The data is inputted currently like "5kg" and there is no space between the 5 and the k, so that is where I am having difficulty. Thank you in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-05-06T15:34:35+00:00

    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..

    Image

    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.

    Image

    Excel will fill in the corresponding data.

    Image

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

    Image

    1 person found this answer helpful.
    0 comments
  2. OssieMac 48,001 Reputation points Volunteer Moderator
    2024-05-06T20:55:43+00:00

    @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

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. OssieMac 48,001 Reputation points Volunteer Moderator
    2024-05-06T02:12:07+00:00

    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)

    Image

    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.

    Image

    0 comments