 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Tab spacing in formula...
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 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 > 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 [quoted text clipped - 13 lines] > Dan 3.20.07 2 > Franky 4.05.07 3 > I imagine you have a formula like: > [quoted text clipped - 34 lines] > > Dan 3.20.07 2 > > Franky 4.05.07 3 Thanks Pete! You're welcome - thanks for feeding back. Pete > > I imagine you have a formula like: > [quoted text clipped - 38 lines] > > - Show quoted text - Untried but this might work =A1&CHAR(9)&B1&CHAR(9)&C1 CHAR(9) is the horizontal tab best wishes  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >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 [quoted text clipped - 13 lines] > Dan 3.20.07 2 > Franky 4.05.07 3 That shows a square box (like char(10)). And it won't disappear if you wrap text. > Untried but this might work > =A1&CHAR(9)&B1&CHAR(9)&C1 [quoted text clipped - 22 lines] > > Dan 3.20.07 2 > > Franky 4.05.07 3 SignatureDave Peterson 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 SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > That shows a square box (like char(10)). And it won't disappear if you > wrap [quoted text clipped - 26 lines] >> > Dan 3.20.07 2 >> > Franky 4.05.07 3 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. --- 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 > That shows a square box (like char(10)). And it won't disappear if you wrap > text. [quoted text clipped - 25 lines] > > > Dan 3.20.07 2 > > > Franky 4.05.07 3 With no existing comment in the activecell, this: activecell.AddComment Text:="Hi" & vbTab & "there" showed a little box between the words. > 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 [quoted text clipped - 40 lines] > > > > Dave Peterson SignatureDave Peterson 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 > > > > >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 [quoted text clipped - 13 lines] > > > > > Dan 3.20.07 2 > > > > > Franky 4.05.07 3 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.