Tab spacing in formula...

[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

 

[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

 

[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

Thanks Pete!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks Pete!- Hide quoted text -

- Show quoted text -

 

[Bernard Liengme]

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

 

[Dave Peterson]

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

 

[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

 

[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.

 

[Dave Peterson]

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

 

[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.