+ Reply to Thread
Results 1 to 7 of 7

trimming space between splitting numbers and text

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    trimming space between splitting numbers and text

    How to trim the space in column B as per formula in red to achieve results in blue font figures

    column a column b column c
    3 456.00EA 3.00 3 456.00EA


    expected results should be 3456 EA

    that column B 3456 WITH NO TRAILING SPACE and column C EA

    Using formula below


    =TEXT(LOOKUP(99^99,--LEFT(AK14,ROW($A$1:$A$2132))),"0.00")

    SUBSTITUTE(AK14,AL14,"")

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: trimming space between splitting numbers and text

    If there is always a 2 character suffix ..

    in B

    =LEFT(SUBSTITUTE($A1," ",""),LEN($A1)-3)+0

    in C

    =RIGHT(SUBSTITUTE($A1," ",""),2)

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: trimming space between splitting numbers and text

    The formula works but I have also 3 character suffix PKS.

    What should be modified in your actual formula

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: trimming space between splitting numbers and text

    What are formats - always 4 digits?:

    nnnnxx

    nnnnxxx

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: trimming space between splitting numbers and text

    See attached combinations EA and PKS
    Attached Files Attached Files
    Last edited by JEAN1972; 07-04-2017 at 12:50 PM.

  6. #6
    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: trimming space between splitting numbers and text

    Enter in B2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then enter in C2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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

  7. #7
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: trimming space between splitting numbers and text

    Alkey

    Thank you formula works as a charm

+ 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] Splitting Text and Numbers from the one cell
    By coach.32 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-28-2015, 04:06 PM
  2. [SOLVED] Splitting numbers and text to their respective columns
    By JEAN1972 in forum Excel General
    Replies: 7
    Last Post: 07-31-2014, 06:48 AM
  3. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  4. Replies: 3
    Last Post: 05-04-2013, 09:57 PM
  5. Trimming Text strings with numbers
    By cloudwalking in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2013, 01:00 PM
  6. [Solved] Removing numbers and space before text
    By loyski in forum Excel General
    Replies: 5
    Last Post: 02-07-2012, 11:36 AM
  7. Splitting numbers separated by a space automatically?
    By barneyc in forum Excel General
    Replies: 5
    Last Post: 12-21-2009, 06:03 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