# Adding a space at the end of a text string

#### TOFW

##### New Member
I need to add a single space to the end of a text string in a column of numbers of varying lengths. (Find and replace is somewhat limited)
Thanks for that which works for any length of number. I forgot to mention that some cells have no number and do not require a space!
What I'm actually trying to achieve but perhaps looking at the problem the wrong way is as follows
column 1 has a house number
column 2 has a street
I can use the following
=CONCATENATE(A1," ",A2)which works fine apart from it adds a space to the front of the result when column 1 has no number
eg
Column 1 is 88
Column 2 is Hill Street

Formula returns 88 Hill Street

Column 1 is Empty
Column 2 is Hill Street

Formula returns (space)88 Hill Street
Which means the address result is not aligned
Any ideas to help?
This message was edited by TOFW on 2002-09-19 02:47

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
=A1 & " "

Thanks for that which works for any length of number. I forgot to mention that some cells have no number and do not require a space!
What I'm actually trying to achieve but perhaps looking at the problem the wrong way is as follows
column 1 has a house number
column 2 has a street
I can use the following
=CONCATENATE(A1," ",A2)which works fine apart from it adds a space to the front of the result when column 1 has no number
eg
Column 1 is 88
Column 2 is Hill Street

Formula returns 88 Hill Street

Column 1 is Empty
Column 2 is Hill Street

Formula returns (space)88 Hill Street
Which means the address result is not aligned
Any ideas to help?

This can simply be avoided using an IF...

IF(A1="",B1,A1&" "&B1)

Thanks for your help I'll get to grips with formulas eventually!!

Replies
9
Views
916
Replies
5
Views
714
Replies
11
Views
505
Replies
0
Views
113
Replies
4
Views
892

1,219,792
Messages
6,150,291
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back