0

I have the data below:

enter image description here

I would like to locate the maximum length of the string of texts in the first column, and from there count the number of spaces to position the second concatenated item to align. My attempt to concatenate has the below formula

=A1&REPT(" ",40-LEN(A1))&TEXT(B1,"0.00%")

and only got this so far as the format:

enter image description here

I want to be able to achieve this:

enter image description here

Any ideas?

kenkenhimself
  • 71
  • 1
  • 13
  • 2
    You will need to use a [Monospaced font](https://en.wikipedia.org/wiki/Monospaced_font) – Scott Craner Oct 19 '16 at 19:27
  • Why not just remove the border between the cells so it looks like one cell and align the numbers left? – Scott Craner Oct 19 '16 at 19:28
  • I have special reasons for concatenating the columns. I discovered that the spaces from concatenated columns can be retained if you post the data on Skype, compared to manually adding spaces in Excel, which does not. – kenkenhimself Oct 19 '16 at 19:32
  • Are you required to have merged cells? Or the data in one cell? You could put the words in one column, and their numbers next to it, then just format (via coloring/borders) the cells to look as one? – BruceWayne Oct 19 '16 at 19:34
  • 1
    Then you will need to use a monospaced font. – Scott Craner Oct 19 '16 at 19:35
  • Another idea is to get the `len()` of just your text. Then find out the max characters that will fit in your cell. Then you can just add spaces with `totalCharactersAllowed - Len([text[)`, if that makes sense? – BruceWayne Oct 19 '16 at 19:44
  • @BruceWayne one cell to concatenate 5 columns. This is actually for posting on Skype. Having a hard time 'formatting' the columns for columns to correctly display – kenkenhimself Oct 19 '16 at 19:51
  • @kenkenhimself "for posting on Skype" - what's your ultimate goal with the concatenating? To easily copy into Skypes text field? We may have a little bit of an X/Y Problem. – BruceWayne Oct 19 '16 at 19:58
  • @BruceWayne Easily copy into Skype with the format for each column. I meantioned this to Scott: "I have special reasons for concatenating the columns. I discovered that the spaces from concatenated columns can be retained if you post the data on Skype, compared to manually adding spaces in Excel, which does not." – kenkenhimself Oct 19 '16 at 20:09
  • If you want a consisten outcome, you should definitely look at using a mono-spaced font like Scott mentioned twice already. – Robin Mackenzie Oct 20 '16 at 05:17
  • Hi @RobinMackenzie, my bad for not acknowledging Scott's suggestion, which I've already taken into consideration. My problem now is to retain the format and column spacing when pasting the data to Skype. Thanks! – kenkenhimself Oct 22 '16 at 00:51
  • Great, thanks for this, @ScottCraner! – kenkenhimself Oct 22 '16 at 00:52

0 Answers0