Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 59
Default Remove space at beginning of each name

I recieved a list that has several hundred names that I was going to do a
VLOOKUP on to put in some data from another spreadsheet. The list I received
has one space at the beginning of each name so the VLOOKUP won't work. How
can I remove this space without going into every single cell and deleting?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Remove space at beginning of each name

Say the names are in A1 thru A1000. In B1 enter:
=TRIM(A1) and copy down

You can then copy column B and PasteSpecialValue back onto column A if you
like.
--
Gary''s Student - gsnu200768


"Supe" wrote:

I recieved a list that has several hundred names that I was going to do a
VLOOKUP on to put in some data from another spreadsheet. The list I received
has one space at the beginning of each name so the VLOOKUP won't work. How
can I remove this space without going into every single cell and deleting?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Remove space at beginning of each name

You could use:

Select the column
Data|Text to columns
Fixed width (but remove any lines that excel guessed)
and finish up.

But both leading and trailing spaces will be chopped off. Multiple internal
spaces won't be changed.

=trim() will get rid of leading/trailing and multiple internal spaces.

Supe wrote:

I recieved a list that has several hundred names that I was going to do a
VLOOKUP on to put in some data from another spreadsheet. The list I received
has one space at the beginning of each name so the VLOOKUP won't work. How
can I remove this space without going into every single cell and deleting?


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Remove space at beginning of each name

how about

for each c in range("a2:a22")
c.value=ltrim(c)
next c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Supe" wrote in message
...
I recieved a list that has several hundred names that I was going to do a
VLOOKUP on to put in some data from another spreadsheet. The list I
received
has one space at the beginning of each name so the VLOOKUP won't work.
How
can I remove this space without going into every single cell and deleting?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to remove blank space? Daniel Charts and Charting in Excel 1 August 6th 07 08:23 AM
How to remove a space Kevin Excel Discussion (Misc queries) 5 July 20th 07 01:24 PM
eliminate space at beginning of line Finger Tips Excel Worksheet Functions 1 June 1st 07 12:55 AM
Remove Spaces beginning Time entry Damon Longworth Excel Discussion (Misc queries) 10 June 19th 05 04:37 PM
How do I remove a blanck space from the beginning of a formula? kan2953 Excel Worksheet Functions 6 April 6th 05 05:38 PM


All times are GMT +1. The time now is 02:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"