I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name and
remove the spaces?

Thanks for your help.

Nikki

=trim(a3)&" " &b3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nikki" wrote in message
...
I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name
and
remove the spaces?

Thanks for your help.

Nikki

Just to ADD on

=LTRIM(" A ") or Left Trim will result in "A "
=RTRIM(" A ") or Right Trim will result in " A"
TRIM will do the trimming from both sides

If this post helps click Yes
---------------
Jacob Skaria


"Nikki" wrote:

I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name and
remove the spaces?

Thanks for your help.

Nikki

Both ltrim and rtrim will work in code, but they're not functions built into
excel.

Maybe you have built your own UDF's to do this???

Jacob Skaria wrote:

Just to ADD on

=LTRIM(" A ") or Left Trim will result in "A "
=RTRIM(" A ") or Right Trim will result in " A"
TRIM will do the trimming from both sides

If this post helps click Yes
---------------
Jacob Skaria

"Nikki" wrote:

I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name and
remove the spaces?

Thanks for your help.

Nikki

--

Dave Peterson

And, just to add. I find that the code trims don't always work well so I
tend to use

application.trim(range("a1")) in my coding.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave Peterson" wrote in message
...
Both ltrim and rtrim will work in code, but they're not functions built
into
excel.

Maybe you have built your own UDF's to do this???

Jacob Skaria wrote:

Just to ADD on

=LTRIM(" A ") or Left Trim will result in "A "
=RTRIM(" A ") or Right Trim will result in " A"
TRIM will do the trimming from both sides

If this post helps click Yes
---------------
Jacob Skaria

"Nikki" wrote:

I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the
two
names. Is there a formula that will give me only the letters in the
name and
remove the spaces?

Thanks for your help.

Nikki

--

Dave Peterson

And just to add (squared, vbg):

the worksheet function application.trim() and VBA's trim function do different
things.

application.trim(" this is a test ")
would return
this is a test
(a single space between each word and leading/trailing spaces gone)


VBA's trim:
trim(" this is a test ")
would return
this is a test
(removing the leading/trailing spaces, but keeping multiple internal spaces)



Don Guillett wrote:

And, just to add. I find that the code trims don't always work well so I
tend to use

application.trim(range("a1")) in my coding.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave Peterson" wrote in message
...
Both ltrim and rtrim will work in code, but they're not functions built
into
excel.

Maybe you have built your own UDF's to do this???

Jacob Skaria wrote:

Just to ADD on

=LTRIM(" A ") or Left Trim will result in "A "
=RTRIM(" A ") or Right Trim will result in " A"
TRIM will do the trimming from both sides

If this post helps click Yes
---------------
Jacob Skaria

"Nikki" wrote:

I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the
two
names. Is there a formula that will give me only the letters in the
name and
remove the spaces?

Thanks for your help.

Nikki

--

Dave Peterson

--

Dave Peterson

I tried this one and it still leaves the 13 spaces in between.

"Don Guillett" wrote:

=trim(a3)&" " &b3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nikki" wrote in message
...
I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name
and
remove the spaces?

Thanks for your help.

Nikki

Works for me with regular spaces.

Perhaps the spaces are html non-breaking spaces which TRIM will not handle.

Suggest selecting the cells and EditReplace

What: Alt + 0160(on the numpad)

With: nothing

Replace all.


Gord Dibben MS Excel MVP

On Wed, 22 Apr 2009 08:21:02 -0700, Nikki
wrote:

I tried this one and it still leaves the 13 spaces in between.

"Don Guillett" wrote:

=trim(a3)&" " &b3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nikki" wrote in message
...
I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the two
names. Is there a formula that will give me only the letters in the name
and
remove the spaces?

Thanks for your help.

Nikki

In which case they are presumably *not* spaces.
What does =CODE(MID(A3,6,1)) give you? It would be 32 if the characters
were spaces. 160 is a non-breaking space, which TRIM doesn't remove, but
see the other advice you've received in this thread.
--
David Biddulph

"Nikki" wrote in message
...
I tried this one and it still leaves the 13 spaces in between.

"Don Guillett" wrote:

=trim(a3)&" " &b3

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nikki" wrote in message
...
I have a column that has a name "Nikki" followed by 13 spaces. When I
concatenate the first name with the last, I have 13 spaces between the
two
names. Is there a formula that will give me only the letters in the
name
and
remove the spaces?

Thanks for your help.

Nikki