Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How can I remove all spaces following the last character in a cell

Posted on 2013-01-26
Medium Priority
273 Views
How can I remove all spaces following the last character in a cell?  In other words...

"# 264,265 BTMPI, # 266  CLINK                  53 after            "

would end up like...

"# 264,265 BTMPI, # 266  CLINK                  53 after"

(without the quote marks)

--Steve
0
Question by:SteveL13
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 49

Expert Comment

ID: 38822481
0

LVL 43

Expert Comment

ID: 38822488
=IF(LEN(TRIM(A1)),REPLACE(LEFT(A1,LOOKUP(2^15,FIND(RIGHT(TRIM(A1)),A1,ROW(INDIRECT("1:"&LEN(A1)))))),1,FIND(LEFT(TRIM(A1)),A1)-1,""),"")

Courtesy barryhoudini
0

Author Comment

ID: 38824374
=TRIM(your cell address)   didn't do anything.  I copied the formula into the space at the top of the spreadsheet as =TRIM(C2) which is the real field I want trimmed and nothing happened other than =TRIM(C2) appeared in cell D2 ???????????

The same thing happens with...   =IF(LEN(TRIM(A1)),REPLACE(LEFT(A1,LOOKUP(2^15,FIND(RIGHT(TRIM(A1)),A1,ROW(INDIRECT("1:"&LEN(A1)))))),1,FIND(LEFT(TRIM(A1)),A1)-1,""),"")

The formula just appears in D2.

I'm sure I'm doing something wrong.
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 38824464
Check this file
Clean-external-spaces.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll