+ Reply to Thread
Results 1 to 3 of 3

Extract data between spaces

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    260

    Extract data between spaces

    I receive spreadsheets with data in column A which in each single cell looks like:

    C FB 3.00 M 1.40 M 1.20 M

    I need to be able to extract the 'bits' of data so that each one is in its own cell, for intstance "C", "FB", "3.00", "M" etc. I've found that using the "text to columns" function actually does what I need but I need a formula version (or perhaps a macro) rather than something that has to be used by going through the menus.

    Some caveats: so far there are either 7 or 8 "bits", this should stay the same but I can' be sure. Also the number of spaces between the various bits of info is variable, it's not just one space or 10 spaces etc.

    I'm not very good at text functions so would appreciate some help!

    Cheers
    Rob

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Extract data between spaces

    Assuming data is in A1

    UDF solution here
    http://www.extendoffice.com/document...-nth-word.html

    Formula solution
    =TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A1),FIND("^^",SUBSTITUTE(TRIM(A1)&" "," ","^^",B1))-1)," ",REPT(" ",99)),99))
    where B1 is the nth word
    Source: http://www.ozgrid.com/forum/showthread.php?t=150224
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract data between spaces

    Try this one in B1 and pull formula to the right until you see blank cell.

    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    C FB 3.00 M 1.40 M 1.20 M C FB 3.00 M 1.40 M 1.20 M
    Last edited by AlKey; 07-28-2014 at 08:01 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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] Extract Data between Spaces
    By nironto in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-23-2013, 03:46 AM
  2. [SOLVED] Formula to extract a word + 3 spaces
    By Marco-Kun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2012, 03:20 PM
  3. Replies: 6
    Last Post: 10-01-2009, 02:50 PM

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