=CONCATENATE Question

[GDRIII]

I'm using the following formula to string a City State and Zip together.

=
CONCATENATE (
[strCity] & ", ",
CONCATENATE ( [strState] & " ", [strZip] )
)


Instead of spacing with spaces, is there a way to format a standardized indent?
...as if I hit TAB twice let's say

 

[bobsan42]

should be achieved with CHAR(9) but for some reason i can't make it work

 

[bobsan42]

I cannot seem to find a way to insert a TAB character in a cell
Plus i don't think you have any predefined indents for cells in excel.

The only work around i can think of is an m-space CHAR(1)
or use a calculation to determine tha number of spaces you need and the use it to repeat spaces e.g. REPT(" ",5) or REPT(CHAR(1),3)

 

[GDRIII]

I was thinking the same thing on my way in this morning. I am CONCATENATEing the following fields strPosition, strContactID, strPhone, strCellPhone, and strEmail

Sooooo. I set up 3 calculated columns per field I want to CONCATENATE. One for text length =LEN([strContactID]) one for the Max Length =MAX([Contact Length]) and "Gap" columns to tell me how many spaces to insert plus 2 spaces =([Contact Max]-[Contact Length])+2

Then I wrote this MOC... but, the darn things don't line up as I think they should.

=
CONCATENATE (
[strPosition],
CONCATENATE (
REPT ( " ", [Position Gap] ),
CONCATENATE (
[strContactID],
CONCATENATE (
REPT ( " ", [Contact Gap] ) & "P: ",
CONCATENATE (
[strPhone],
CONCATENATE (
REPT ( " ", [Phone Gap] ) & "M: ",
CONCATENATE (
[strCellPhone],
CONCATENATE (
REPT ( " ", [Cell Gap] ) & "Email:",
CONCATENATE ( [strEmail], REPT ( " ", [Email Gap] ) )
)
)
)
)
)
)
)
)

 

[bobsan42]

Well i have to do some real-testing or check some real data and try to see the problem myself,

But First is - make sure you are using a mono-space (fixed width) font e.g. courier or courier new, i think.
Only then will any such calculations make sense.

If you're not using such a font, then every symbol has its own width - meaning a text string's length depends on the actual symbols in it, not only on their number.

Is it confusing?

 

[GDRIII]

Thanks Bob.

Fixed width font did the trick...