+ Reply to Thread
Results 1 to 10 of 10

Problem with spaces between text and numbers

  1. #1
    Registered User
    Join Date
    04-26-2014
    Location
    Finland
    MS-Off Ver
    Excel 2010/2013
    Posts
    6

    Problem with spaces between text and numbers

    Hey,

    I have rows with "numbers" like 1 250,30 and 1 350,50, but they aren't in number format (I guess this is the problem). I am trying to get rid of the extra space between the "numbers" but the substitute or trim function does not work for me. I also tried to divide and multiply the numbers but it does not work.

    Any ideas? I want to get the extra space of and to the number format.
    Last edited by hensoros; 06-19-2014 at 09:55 AM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with spaces between text and numbers

    If Trim or Substitute don't work, then the spaces are not really spaces.
    They're some other non printable character.

    We need to know what character it actually is to proceed.

    Given that number 1 250,30
    What does this return
    =CODE(MID(A1,2,1))

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problem with spaces between text and numbers

    Assume your first piece of data in cell A1, then in B1 type =REPLACE(A1,2,1,)*1 and copy down.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Problem with spaces between text and numbers

    In your description, there is an actual space character in your numbers.
    I suspect that your actual data comes from a website and the "spaces" are actually HTML non-breaking spaces.

    Try this to remove them...

    Excel 2007 and later:
    Home.Find&Replace.Replace
    Find What: [Alt]+0160 <-Hold down [Alt]…type 0160…release [Alt]
    Replace with: (leave this blank)
    Click: [Replace All]

    or...
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Problem with spaces between text and numbers

    Are you sure they're not numbers? The default separators for Finland are the space and comma as your post is showing. If you format the cells as General does the appearance change?
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    04-26-2014
    Location
    Finland
    MS-Off Ver
    Excel 2010/2013
    Posts
    6

    Re: Problem with spaces between text and numbers

    alansidman: =REPLACE(A1,2,1,)*1 function works but as some of the numbers are like 20 130,30 and so on I would have to do a lot of work manually.

    Ron Coderre: it does not let me to write and press alt at the same time

    romperstomper : I tried, it does not work.

    Any more ideas?

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with spaces between text and numbers

    Quote Originally Posted by hensoros View Post
    Ron Coderre: it does not let me to write and press alt at the same time
    You won't see the characters being printed in the find window when you do it, but it IS working.
    Hold ALT, type 0160, release ALT
    You won't see anything in the box except something that looks like a space
    Click replace all


    What did this reveal?
    Quote Originally Posted by Jonmo1 View Post
    Given that number 1 250,30
    What does this return
    =CODE(MID(A1,2,1))

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with spaces between text and numbers

    upload a small sample workbook (no sensitive info)
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Problem with spaces between text and numbers

    The macro at this website will remove all leading/trailing
    and multiple interspersed char 32 space characters.
    It will also remove and/or convert char 160 non breaking
    spaces into standard char 32 space characters. It will
    work on text or numbers and the numbers will be
    converted to true numeric numbers.

    I use this macro dozens of times every single day!
    It's a real time saver.

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problem with spaces between text and numbers

    Give this a try:

    =REPLACE(A1,FIND(" ",A1),1,)*1

+ 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. Add spaces between text and numbers
    By krunk in forum Excel General
    Replies: 3
    Last Post: 07-06-2012, 03:57 PM
  2. Separating Numbers and Text with Spaces
    By hobbitchips in forum Excel General
    Replies: 5
    Last Post: 02-14-2009, 06:36 AM
  3. [SOLVED] How do I convert numbers stored as text with spaces to numbers
    By Baffuor in forum Excel General
    Replies: 1
    Last Post: 05-24-2005, 06:32 AM
  4. Replies: 1
    Last Post: 05-24-2005, 06:31 AM
  5. Replies: 1
    Last Post: 05-24-2005, 03:06 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