+ Reply to Thread
Results 1 to 18 of 18

how to split the numbers in a cell and remove all the spaces at the end of the digits

  1. #1
    Registered User
    Join Date
    01-16-2018
    Location
    taiwan
    MS-Off Ver
    2010
    Posts
    19

    how to split the numbers in a cell and remove all the spaces at the end of the digits

    Hi,

    I want to separate the group numbers in row A, each of them contains 3~5 numbers, I want to separate the first number into row C and the second into row D, and then calculate their average in C24 & D24.

    I've separate them by using "left" function, and convert them from text to number, but it can't calculate the average as every of them are following by some spaces, please help to solve the spaces, thanks.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    there are No-Break Spaces (160) not standard spaces (32)
    Use SUBSTITUTE with your LEFT formula

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Yep. So, in C3, copied across and down:

    =TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A3,CHAR(160)," "))," ",REPT(" ",125)),125*COLUMNS($A:A),125))+0

    and then =AVERAGE(C3:C22) copied across.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    b2=AVERAGE(C2:M2)

    c2=IFERROR(--TRIM(MID(SUBSTITUTE($A2,"**",REPT(" ",52)),(COLUMNS($C2:C2)-1)*52+1,52)),"")
    try the above formula in c2 and copy towards right upto m2
    do the same for the remaining rows
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    why the above post showing ** I don't know
    see the attached file
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Quote Originally Posted by Glenn Kennedy View Post
    Yep. So, in C3, copied across and down:

    =TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A3,CHAR(160)," "))," ",REPT(" ",125)),125*COLUMNS($A:A),125))+0

    and then =AVERAGE(C3:C22) copied across.
    You'll need to wrap that in an IFERROR otherwise when there are less items eg row 13 it throws a #VALUE

    EDIT: TOO SLOW AGAIN!
    If someone has helped you then please add to their Reputation

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Or try:

    =--MID(SUBSTITUTE(SUBSTITUTE($A2,CHAR(160),"")," ",""),COLUMNS($A:A),1)

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Are you afraid of the dark (means PowerQuery)?

    If not - use it
    Attached Files Attached Files

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

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    1) Column A not row A

    2) The separaters are not spaces, they are CHAR(160) which suggests this data has been pulled from the web.

    I dont like doing it this way but it works

    in C2
    =TRIM(MID(SUBSTITUTE(A2,CHAR(160),REPT(" ",50)),1,50))+0
    in D2
    =TRIM(MID(SUBSTITUTE(A2,CHAR(160),REPT(" ",50)),101,50))+0
    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.

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Actually, it isn't space character. It's multiple Unicode character (Non-Breaking space) between each number.

    One way to easily split these numbers is to use Text to column.

    In some out of way cell, enter following formula.
    =UNICHAR(160)

    Then copy and paste as value into another cell. Go into cell and copy the content.

    Go to Data ribbon tool, click on Text to Columns.

    Select Delimited, hit next, check "Other" and paste in the copied value. Make sure you check "Treat consecutive delimiters as one".

    0.JPG

    Done.

    If you want to do it using formula...
    In C2: Copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In D2: Copy across to G2 and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached.

    Edit: Darn everyone's so fast
    Attached Files Attached Files
    Last edited by CK76; 01-18-2018 at 09:23 AM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  11. #11
    Registered User
    Join Date
    01-16-2018
    Location
    taiwan
    MS-Off Ver
    2010
    Posts
    19

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    I copy your formula
    TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A3;CHAR(160);" "));" ";REPT(" ";125));125*COLUMNS($A:A);125))+0
    into my excel workbook, it doesn't work
    even go back the the excel u did for me, when I click in the cell C3, no matter press enter or shift+ctrl+enter, it becomes an error, the formula is totally the same, really can't get the reason.
    Attached Files Attached Files

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    When I open the attachment at Post 12.... it works perfectly for me. However, I see you changed the separators from , to ; in the TEXT accompanying Post 12. Do you use ; in Taiwan?? If not, why did you change them??
    Last edited by Glenn Kennedy; 01-18-2018 at 09:54 AM.

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

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Quote Originally Posted by curly7777 View Post
    I copy your formula
    TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A3;CHAR(160);" "));" ";REPT(" ";125));125*COLUMNS($A:A);125))+0
    into my excel workbook, it doesn't work
    even go back the the excel u did for me, when I click in the cell C3, no matter press enter or shift+ctrl+enter, it becomes an error, the formula is totally the same, really can't get the reason.
    When I open your file (without changing anything) it works perfectly.

  14. #14
    Registered User
    Join Date
    01-16-2018
    Location
    taiwan
    MS-Off Ver
    2010
    Posts
    19

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    I don't know, i'm using excel 2010 which is plug-in my laptop and I didn't change anything, well I'm curious too why ; instead of , (if i use , the formula will become error)

    4.png

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

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    Replace commas with semi colons and see if that works.
    Looks like semi colons are for your version of Excel, not commas.

    As Glen said in post #12

  16. #16
    Registered User
    Join Date
    01-16-2018
    Location
    taiwan
    MS-Off Ver
    2010
    Posts
    19

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    yes I have replaced with semi colons already as u can see in C3 of post#14.
    in the pic C5:C22 was original by Mr Glen, I only click C3 & C4 to copy the formula(without changing nothing), after that I pressed enter, it comes out error

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    the formula I posted and the formula in my attachment contained , not ; So... change them back to , I do not know what made you think that you had to change the , to ; at all!!

    As far as I know it is only mainland Europe that uses ;

  18. #18
    Registered User
    Join Date
    01-16-2018
    Location
    taiwan
    MS-Off Ver
    2010
    Posts
    19

    Re: how to split the numbers in a cell and remove all the spaces at the end of the digits

    actually when I download the file you've sent, it shows ; for all the formula already.
    the one i change to ";", it was the one I copy from this forum post #3.

+ 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] Remove Spaces between Numbers
    By meabrams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2015, 02:41 AM
  2. [SOLVED] Formula to remove spaces between numbers?
    By kellyfirth in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2015, 12:13 PM
  3. How to remove the first 2 digits if they are certain numbers
    By ktech in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-30-2014, 11:42 AM
  4. [SOLVED] Remove spaces in pasted numbers
    By Jonathan9 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-14-2014, 01:15 PM
  5. How to keep spaces between $ and other digits in cell entries?
    By Mirisage in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 09-03-2013, 05:27 PM
  6. Replies: 4
    Last Post: 03-07-2008, 05:21 PM
  7. Split numbers with spaces
    By APD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-20-2005, 01:55 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