How can I 'align right' the results of Excel's "Text to Columns" feature?
Some_Guy
If I am using the Text to Columns feature on the following data, using ";" as a delimiter:
foo;bar;qux;baz;toast;
quux;jam;beans;
I'll end up with the results "left aligned" in the resulting grid of cells:
|foo |bar |qux |baz |toast |
|quux |jam |beans | | |
However, I want them to be "right aligned":
|foo |bar |qux |baz |toast |
| | |quux |jam |beans |
How can I do this?
NOTE: I know that "right-aligned" might not be the correct term, instead implying
| foo| bar| qux| baz| toast|
| quux| jam| beans| | |
but this isn't what I'm seeking. So, if anyone can suggest a better term for what I'm describing, please do so.
Addendum: As an alternative approach, if anyone knows a way to use Excel to rearrange cells such that
|a |b |c |d | | | | | |
|n |m |o |p |q | | | | |
|e |f |g |h |i |j |k |l | |
|n |m |o |p |q | | | | |
|x | | | | | | | | |
becomes
| | | | | |a |b |c |d |
| | | | |n |m |o |p |q |
| |e |f |g |h |i |j |k |l |
| | | | |n |m |o |p |q |
| | | | | | | | |x |
then that would also work.
hBy2Py
The following formulas will allow quick conversion of your data to a form that Text-to-Columns will readily parse right-justified as you describe:
D5 formula (appends a semicolon if absent):
=IF(RIGHT(B5,1)<>";",B5&";",B5)
G5 formula (prepends necessary number of semicolons):
=REPT(";",5-(LEN(D5)-LEN(SUBSTITUTE(D5,";",""))))&D5
Copying the results followed by a Paste-Special-as-Values should afford raw material suitable for a Text-to-Columns conversion.
The solution depends on there being a fixed maximum number of columns; here, five. The formula of G5 could be generalized by adding a 'number of columns to generate' cell elsewhere on the sheet and referencing this new cell instead of the hard-coded 5 value.
Additionally, if you are guaranteed that the data will always have the trailing semicolon, the intermediate step of D5:D7 is superfluous.
EDIT: Per Some_Guy's observation in the comments, the method will also work if all rows are constructed to lack a trailing semicolon.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
TOP Ranking
- 1
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 2
Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)
- 3
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 4
pump.io port in URL
- 5
Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext
- 6
BigQuery - concatenate ignoring NULL
- 7
ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3
- 8
ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements
- 9
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 10
How to remove the extra space from right in a webview?
- 11
java.lang.NullPointerException: Cannot read the array length because "<local3>" is null
- 12
Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');
- 13
flutter: dropdown item programmatically unselect problem
- 14
How to use merge windows unallocated space into Ubuntu using GParted?
- 15
Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd
- 16
Nuget add packages gives access denied errors
- 17
Svchost high CPU from Microsoft.BingWeather app errors
- 18
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 19
12.04.3--- Dconf Editor won't show com>canonical>unity option
- 20
Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?
- 21
maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class
Comments