adding spaces in front of text and "alt enter:" tabs in one cell (address)

[JSH720]

I have an address that is in one cell formatted with:
Name
address
address2
city state zip

There is a hard tab (alt tenter) between the lines after the first line. (the bolded lines) I need to keep everything in one cell, and add five spaces in front of each line. I need to be able to do this with a formula (for clients that won't let me use macros) and as a macro that I can use on various spreadsheets

Any help is appreciated

Thank you.

 

[Special-K99]

Cells contain either a value or a formula, not both.
Since you have a value already in those cells the only way to do this would be by either using a helper column (a column that has a formula and refers to column A) or VBA.
So in column B you would have

=" "&SUBSTITUTE(A1,CHAR(10),CHAR(10)&" ")
(the forum has reduced my 5 spaces to a single space)
and format the cell as per column A.

Or use VBA.

 

[JSH720]

Thanks, I'll try it. VBA would be awesome for when I can use it.

 

[Special-K99]

I'm not a VBA expert I'm afraid so this is all I can supply.