Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to work out how to remove the the data from the column in left hand side so that it is shown in the right hand column.
    The data highlighted in black in the first column will vary in length. I do not require any data in the first column which is shown after the first space.
    i.e I only need data 102.194 from the first column and moved into the second.
    See Screenshot (2nd one i.e last one as 1st one is wrong)

    Also , when l move the data across to the right column l need to remove the full stop as this character is NOT required, so 102.194 should be 102194.
    Attached Images Attached Images

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:
    =SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","")

    If this does not solve your problem, please post a workbook with simple data and the desire result. Workbooks are much easier to work with than screen shots.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One probable solutions is to split data using "Text to Cloumns" options and then move the column to desired locations.
    Regards
    Prasad

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='793288' date='14-Sep-2009 15:55']Try this:
    =SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","")

    If this does not solve your problem, please post a workbook with simple data and the desire result. Workbooks are much easier to work with than screen shots.[/quote]


    That works fine, but the problem is is that there is a space before the data starts see attached spreadsheet for example.
    If you could resolve this , that would be great.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post the book saved to an earlier version of Excel. I do not have access to E2007

  6. #6
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='793363' date='15-Sep-2009 13:37']Can you post the book saved to an earlier version of Excel. I do not have access to E2007[/quote]


    see atatched.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by prasad' post='793341 View Post
    T..... problem is is that there is a space before the data starts .....
    Based on your sheet the Trim function will solve this.

    =SUBSTITUTE(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),".","")

Posting Permissions

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