find a space; replace w/no space?

[el zorro]

I have a very large dataset with a "Name" field that
people have been using with this format: Lastname,
Firstname. (Note the space after the comma.) I want to
reconfigure the table with 2 separate fields, one for
First Name and one for Last Name, so I did the "Text to
Columns" thing and split the data into two fields.

However, all of the new entries for First Name are now
preceded by a space, inherited from the previous format.
We're talking thousands of entries here, so not really
feasible to manually go into each record and remove the
leading space. (I'm getting ready to move all the data
into Access, so I don't want to leave those pesky spaces
in there.) Is there a Find and Replace way to do this?
What is the character for "space"? Any ideas? Thanks!

 

[gerryR]

Just highlight the colum and go to "find and replace" in the find column
put a space and in the replace column put nothing. It works for me (excel
2000)

hth
gerryR

 

[Frank Kabel]

Hi
just hit the spacebar in the Find textbox and leave the Replace textbox
empty

 

[mzehr]

Hi,
When using find and replace, use a space in the find field
and nothing in the replace. However, if the last name
legitimately has a space in it it will be removed using
this method. You might want to consider using a helper
column and the Trim() function instead.

 

[Guest]

Try this...
Rebuild the FirstName cells by creating a NewFirstName
column containing: =RIGHT(FirstNamecellref,LEN
(FirstNamecellref)-1)
Replace the calculated NewFirstName cells with
Edit/Copy, then Edit/PasteSpecial/Values

You could use Edit/Replace, but I don't recommend it
because you might replace non-offending spaces in things
such as compound names where the space should be left
alone. FYI, the procedure is to type a space with the
spacebar in the "Find what" dialog block, and leave
the "Replace with" block empty. Very dangerous unless you
want to squash ALL spaces!

Hope this helps.

 

[RagDyer]

You've got this problem because you didn't use "TextTo Columns" to it's
*fullest* capabilities.

So, the next time you might need to do this, OR, if you still have your
original data list and wish to try again, all you have to do is, in the
second window of TTC, you can check *both* "space" *and* "comma" as
delimeters, and as insurance, you can even check "Treat Consecutive
Delimeters As One".

This will give you two columns without any extra spaces.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have a very large dataset with a "Name" field that
people have been using with this format: Lastname,
Firstname. (Note the space after the comma.) I want to
reconfigure the table with 2 separate fields, one for
First Name and one for Last Name, so I did the "Text to
Columns" thing and split the data into two fields.

However, all of the new entries for First Name are now
preceded by a space, inherited from the previous format.
We're talking thousands of entries here, so not really
feasible to manually go into each record and remove the
leading space. (I'm getting ready to move all the data
into Access, so I don't want to leave those pesky spaces
in there.) Is there a Find and Replace way to do this?
What is the character for "space"? Any ideas? Thanks!