Tool to replace tabs with VARIABLE NUMBER OF SPACES and preserve column alignment?
-
I have a table in Excel (i.e. tab-separated data) that I want to copy-and-paste into a C-file. I would like the table to be readable in the C-file as well, i.e. it should be nicely formatted into aligned columns. If I do a "dumb" replace-tabs-with-spaces then my column alignment gets messed up due to the text in the cells not being equal width. Does anybody know of a tool that can convert my tabs with a variable number of space and preserve the nice column alignment?
It is no good replacing tabs with some number of spaces. Tabs are markers which indicate that the next item should be placed on a tab boundary. Boundaries are on the next column which is a multiple of the tab width (usually 4 or 8). So a tab may represent any number of spaces from 1 up to the tab width. Are you pasting this as comments in your C file or as data to be displayed? If the latter then the tab characters should work.
-
I have a table in Excel (i.e. tab-separated data) that I want to copy-and-paste into a C-file. I would like the table to be readable in the C-file as well, i.e. it should be nicely formatted into aligned columns. If I do a "dumb" replace-tabs-with-spaces then my column alignment gets messed up due to the text in the cells not being equal width. Does anybody know of a tool that can convert my tabs with a variable number of space and preserve the nice column alignment?
Since the process involves the use of the clipboard, there are a couple of solutions which come to my mind:
- You could create a VBA macro in Excel which would format the contents:
-
You would need some PADLEFT or PADRIGHT kind of function:
Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
End FunctionFunction PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
End Function -
Then use one of these functions to pad the values to the width of the largest value and place the result in the clipboard.
-
Have a button in Excel which launches the macro (whose result will be formatted text exported to the clipboard).
-
- Or find an utility which can automatically format the content of the clipboard according to a specified format string. Honestly, I have never searched for such a tool, I don't even know if that exists.
"Five fruits and vegetables a day? What a joke! Personally, after the third watermelon, I'm full."
- You could create a VBA macro in Excel which would format the contents:
-
It is no good replacing tabs with some number of spaces. Tabs are markers which indicate that the next item should be placed on a tab boundary. Boundaries are on the next column which is a multiple of the tab width (usually 4 or 8). So a tab may represent any number of spaces from 1 up to the tab width. Are you pasting this as comments in your C file or as data to be displayed? If the latter then the tab characters should work.
-
Not necessarily if the widths accross a single column vary by more than a single tabulation's size.
"Five fruits and vegetables a day? What a joke! Personally, after the third watermelon, I'm full."
-
Imagine the following case:
V1 V2 V3
longv1 longv2 longv3It seems that OP's values (in a single column) have disparate widths. The same kind of "issue" which leads to code-blocks like
int value = 0;
char* name = "dummy";
vector<char> characters = /* ... */;"Five fruits and vegetables a day? What a joke! Personally, after the third watermelon, I'm full."
-
Imagine the following case:
V1 V2 V3
longv1 longv2 longv3It seems that OP's values (in a single column) have disparate widths. The same kind of "issue" which leads to code-blocks like
int value = 0;
char* name = "dummy";
vector<char> characters = /* ... */;"Five fruits and vegetables a day? What a joke! Personally, after the third watermelon, I'm full."
-
I have a table in Excel (i.e. tab-separated data) that I want to copy-and-paste into a C-file. I would like the table to be readable in the C-file as well, i.e. it should be nicely formatted into aligned columns. If I do a "dumb" replace-tabs-with-spaces then my column alignment gets messed up due to the text in the cells not being equal width. Does anybody know of a tool that can convert my tabs with a variable number of space and preserve the nice column alignment?
It is impossible only excel knows how wide the original columns were, that is why it puts the delimiters in the text to mark the columns :-) So if you want the original column width in excel you need to get excel to put them in a cell so it gets exported with the data and you can then use it to reformat the layout. I know for say cell A1 width the excel formula is =CELL("width", A1) which displays as a decimal number. What exactly that number is I have no idea but you can try searching for it or just play with a few letters and font heights and you should be able to work it out.
In vino veritas
-
It is impossible only excel knows how wide the original columns were, that is why it puts the delimiters in the text to mark the columns :-) So if you want the original column width in excel you need to get excel to put them in a cell so it gets exported with the data and you can then use it to reformat the layout. I know for say cell A1 width the excel formula is =CELL("width", A1) which displays as a decimal number. What exactly that number is I have no idea but you can try searching for it or just play with a few letters and font heights and you should be able to work it out.
In vino veritas
-
I have a table in Excel (i.e. tab-separated data) that I want to copy-and-paste into a C-file. I would like the table to be readable in the C-file as well, i.e. it should be nicely formatted into aligned columns. If I do a "dumb" replace-tabs-with-spaces then my column alignment gets messed up due to the text in the cells not being equal width. Does anybody know of a tool that can convert my tabs with a variable number of space and preserve the nice column alignment?
I have found exactly what I was looking for. I if go to the website ASCII Table Generator – Quickly format ASCII table. Great for source code comments and markdown![^] and choose "Header Location:" "None" and "Output Style:" "ASCII (Compact)" then I get exactly the formatting I want. Thanks everyone who contributed in this thread.
-
I don't mean the width in pixels or whatever unit Excel is using, I mean width in number of characters.
I know but you aren't really looking at problem. The fonts in the cells are true type proportional not old school fixed pixel fonts so unless you have display formats on them you are dead out luck they wont be fixed character widths. So the best you can probably do is the pixel width and then divid it by some number so if the font sort of averages 10 pixels width the 100 pixel column = 100/10 = 10 characters and 150 pixel width column gives you 15 characters. You aren't going to be able to do much better than that at at least it will be somewhat columnized
In vino veritas
-
I know but you aren't really looking at problem. The fonts in the cells are true type proportional not old school fixed pixel fonts so unless you have display formats on them you are dead out luck they wont be fixed character widths. So the best you can probably do is the pixel width and then divid it by some number so if the font sort of averages 10 pixels width the 100 pixel column = 100/10 = 10 characters and 150 pixel width column gives you 15 characters. You aren't going to be able to do much better than that at at least it will be somewhat columnized
In vino veritas