+ Reply to Thread
Results 1 to 6 of 6

Text manipulation

  1. #1
    Registered User
    Join Date
    02-26-2007
    Posts
    2

    Text manipulation

    Hi, I'm new to this so forgive any incorrect protocol.
    I am trying to extract data from text (strings) such as "W: 100 cm, D: 58 cm, H: 72 cm" the text (between inverted commas) is in one cell in Excel 2003and, in order to use the data, I would like to extract the 100, 50, and 72 into three cells.
    Can anyone help?
    Regards,
    Pete

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Make sure you have vacant columns to the right of your text

    Select cell/cells, column

    If all sizes are 2 digit
    Data Menu > Text to Columns > Fixed Width > Click Where You want to split text (If In wrong place click on line to remove)

    If they are different then try

    Data Menu > Text to Columns > Delimited > Space

    This will split it over 6 columns

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    here's a formula approach. Assuming all your data is in the format shown then use this formula to extract the Width

    =MID(A1,FIND("W",A1)+3,FIND("cm",A1,FIND("W",A1)+1)-FIND("W",A1)-4)+0

    for D and H replace the 3 Ws in the formula with the relevant letter

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Just adding to daddylonglegs approach
    Attached Files Attached Files
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  5. #5
    Registered User
    Join Date
    02-26-2007
    Posts
    2

    Smile Thanks

    Thanks for your help, with slight changes it has proved just the answer.
    I wanted to thank all those who replied but it's taken me half an hour just to find you...Can you direct me to a page that easily tells me how to post a question, and how to see the replies to the question?
    Sorry to be such a dummy, it's my first time on a forum (didn,t know they existed).
    Again, Thanks,
    Pete

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi Pete,

    glad you got it sorted, here's a link to FAQ's

    http://www.excelforum.com/faq.php?fa..._user_maintain

    Also you can click on any of the heading at the top of the web page i.e.
    Excel Help Forum - lists all the forums

    New Posts - All the latest posts in all the forums

    Quick Links etc. etc.

    Also when you post a question be sure to allow in you User CP for emails from the forum that someone has replied, then you can just click on the link in the email to be directed straight back to you question.

    Hope this helps and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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