+ Reply to Thread
Results 1 to 6 of 6

How can I split data in cells.

  1. #1
    Registered User
    Join Date
    10-22-2020
    Location
    Florida
    MS-Off Ver
    Office 2016
    Posts
    2

    How can I split data in cells.

    Good morning.

    I have a problem that I'm not sure whether it is possible for excel to fix. I have a program that will spit out data with a column that combines values and units. I would like to filter and total these values but since the units are listed in the same cell as the values I don't know how. What I am looking for is some function that can read the cell and filter out the unit leaving only the value in another cell. I can live with just pulling out the value but also pulling out the units would be handy.

    I apologize for perhaps using some incorrect terminology as I am very much not an expert with excel and any assistance would be greatly appreciated. Thank you.
    Attached Files Attached Files
    Last edited by Harboe; 10-22-2020 at 08:16 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,337

    Re: How can I split data in cells.

    Welcome to the forum.

    In K8 copied down:

    =--LEFT(Table14[@Amount],FIND(" ",Table14[@Amount])-1)

    In L8 copied down:

    =MID(Table14[@Amount],FIND(" ",Table14[@Amount])+1,99)

    If you have a European locale, change the commas to semi-colons.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: How can I split data in cells.

    So you want to split it like: 1st - to space, 2nd - from space to the end (according to example).

    I
    J
    K
    8
    0.125 lb 0.125 lb
    9
    1.5 gal 1.5 gal
    10
    2,536 fl oz 2,536 fl oz
    11
    2.5 gal 2.5 gal
    12
    24 fl oz 24 fl oz
    13
    320 fl oz 320 fl oz
    14
    38.5 lb 38.5 lb
    15
    45,096 fl oz 45,096 fl oz
    16
    5 lb 5 lb


    I
    J
    K
    8
    0.125 lb =TRIM(LEFT(Table14[@Amount],FIND(" ",Table14[@Amount]))) =TRIM(MID(Table14[@Amount],FIND(" ",Table14[@Amount]),LEN(Table14[@Amount])))
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Registered User
    Join Date
    10-22-2020
    Location
    Florida
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: How can I split data in cells.

    Thank you very much. I am continually amazed at what this program can do. I appreciate the help immensely.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,337

    Re: How can I split data in cells.

    My version of the first formula will leave you with real values, not just text.

    You're welcome!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How can I split data in cells.

    ...another option

    Select the data and the use TextToColumn functionality with the space as a delimiter.
    Then if necessary join strings like "fl" & "oz" now in two columns into one cell
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Column Row, split some data within row cells, HOW??
    By excelboxnovice in forum Excel General
    Replies: 5
    Last Post: 03-15-2016, 10:46 PM
  2. [SOLVED] Split column of cells, each cells data is separated by commas into separate rows
    By mike_m1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-09-2014, 08:33 AM
  3. [SOLVED] Split one cell data into different cells
    By Firoz Khan in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-01-2013, 02:22 PM
  4. [SOLVED] is there a way to split a data cell into two cells
    By CharlieZangel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 02:31 PM
  5. Split data in cells
    By arvin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2010, 10:15 AM
  6. Split Data in 4 cells
    By Burrelly in forum Excel General
    Replies: 7
    Last Post: 11-20-2009, 11:06 AM
  7. Split data from 1 cell into 2 cells
    By Badvgood in forum Excel General
    Replies: 2
    Last Post: 06-17-2009, 11:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1