Tab spacing in formula...

G

Gojavid

I have a sheet that pulls info from several other fields to create a
text string. The problem is that I need all of the values from my
other cells to be a tab space apart. Right now they are separated by
several spaces, but it is throwing off my numbers.


example, what I have:

Name Date Value
John 2.20.07 1
Dan 3.20.07 2

example, what I need:

Name Date Value
John 2.20.07 1
Dan 3.20.07 2
Franky 4.05.07 3
 
P

Pete_UK

I imagine you have a formula like:

=A1&" "&B1&" "&C1

so you are putting a fixed number of spaces between each value. If
this is the case then you could improve things by using the REPT
function, as follows:

=A1&REPT(" ",10-LEN(A1))&B1&REPT(10-LEN(B1))&C1

This will give you a variable number of spaces, dependent on the
length of each value. It won't look completely right, however, if you
use a proportional font like Arial - better to use something like
Courier. If you have numeric values, you might like to right-align
these, taking into account the integer values and the number of
decimal places - you could use the TEXT function here.

Hope this helps.

Pete
 
G

Gojavid

I imagine you have a formula like:

=A1&" "&B1&" "&C1

so you are putting a fixed number of spaces between each value. If
this is the case then you could improve things by using the REPT
function, as follows:

=A1&REPT(" ",10-LEN(A1))&B1&REPT(10-LEN(B1))&C1

This will give you a variable number of spaces, dependent on the
length of each value. It won't look completely right, however, if you
use a proportional font like Arial - better to use something like
Courier. If you have numeric values, you might like to right-align
these, taking into account the integer values and the number of
decimal places - you could use the TEXT function here.

Hope this helps.

Pete
Click to expand...

Thanks Pete!
 
B

Bernard Liengme

Untried but this might work
=A1&CHAR(9)&B1&CHAR(9)&C1
CHAR(9) is the horizontal tab
best wishes
 
D

Dave Peterson

That shows a square box (like char(10)). And it won't disappear if you wrap
text.
 
B

Bernard Liengme

Odd: when you copy and paste to Word the tab becomes a space but if you save
sheet as PRN file the tab is a tab
 
D

David McRitchie

It would probably work in a comment but not in a cell,
but I think it is going to be left just justified at the tab
and probably still won't line up unless your columns
entries are pretty much the same size.
 
D

Dave Peterson

With no existing comment in the activecell, this:
activecell.AddComment Text:="Hi" & vbTab & "there"
showed a little box between the words.
 
D

David McRitchie

I guess this is getting to far off from what you want
it works in a MsgBox
http://www.mvps.org/dmcritchie/excel/#MsgSheetStats

But I don't really understand why you are not simply
putting such items into their own cells. Gridlines or
borders are optional.
http://www.mvps.org/dmcritchie/excel/gridline.htm

You could create another worksheet strictly for
reporting purposes.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 
Top