Help me separate a number and a formatted text into two columns

[azzzy]

Greetings! I have a column in a spreadsheet with cells each custom formatted to display a number (indicating quantity) and a unit of measure (such as lb., kg, can, pc, etc.). I need to separate this column into two formatted as number and text displaying the numeric value for the quantity and the alpha value for the unit of measure. For example, if I have A1 contains 150, formatted as #,##0,000 "LB", and displaying 150.000 LB, I need A1 to be formatted as Number and contain/display 150, and B1 to be formatted as Text and contain/display LB. Every cell in the column is formatted differently depending on the unit of measure. For example, the cells with the unit of measure "PC" are formatted as #,##0 "PC". I have no idea how to go about this. Any help will be greatly appreciated! Thanks much!

 

[Tetra201]

Here is one way of doing this:

Go to Formulas --> Name Manager --> New... In the field "Name:", enter Unit. In the field "Refers to:", paste the following formula:

=SUBSTITUTE(MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99),"""","")

Now, enter =Unit in cell B1 and drag-copy it down as needed. Column B becomes populated with the units of measure.

Next, copy and paste column B onto itself as Values. Delete the Unit name from the Name Manager. Finally, format column A as general.

 

[azzzy]

Here is one way of doing this:

Go to Formulas --> Name Manager --> New... In the field "Name:", enter Unit. In the field "Refers to:", paste the following formula:

=SUBSTITUTE(MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99),"""","")

Now, enter =Unit in cell B1 and drag-copy it down as needed. Column B becomes populated with the units of measure.

Next, copy and paste column B onto itself as Values. Delete the Unit name from the Name Manager. Finally, format column A as general.

I am beyond impressed!!! Thank you so much! It worked! I must admit I have no idea how. If it's not too much bother, can you please explain what exactly and how this formula does?

 

[Tetra201]

You are most welcome.

Here is how it works:
- Let's assume cell A1 is custom-formatted as #,##0 "PC";
- Let's assume =Unit is entered in cell B1;
- INDIRECT("RC[-1]",FALSE) refers to the immediate left cell in the same row, that is A1;
- GET.CELL(7,INDIRECT("RC[-1]",FALSE)) retrieves the format of cell A1, that is #,##0 "PC";
- FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))) finds the position of the first double quote in #,##0 "PC", which is 7;
- MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99) retrieves the string from position 7 to the end of the format, that is "PC";
-finally, SUBSTITUTE(MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99),"""","") strips both double quotes from "PC", so the cell B1 displays just PC.

 

[azzzy]

You are most welcome.

Here is how it works:
- Let's assume cell A1 is custom-formatted as #,##0 "PC";
- Let's assume =Unit is entered in cell B1;
- INDIRECT("RC[-1]",FALSE) refers to the immediate left cell in the same row, that is A1;
- GET.CELL(7,INDIRECT("RC[-1]",FALSE)) retrieves the format of cell A1, that is #,##0 "PC";
- FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))) finds the position of the first double quote in #,##0 "PC", which is 7;
- MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99) retrieves the string from position 7 to the end of the format, that is "PC";
-finally, SUBSTITUTE(MID(GET.CELL(7,INDIRECT("RC[-1]",FALSE)),FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))),99),"""","") strips both double quotes from "PC", so the cell B1 displays just PC.

Awesome! Again, thank you so much!

 

[azzzy]

Actually there is still one thing I don't understand. So if FIND("""",GET.CELL(7,INDIRECT("RC[-1]",FALSE))) finds the position of the first quote, which is 7, how does it also work with other formatted cells like #,##0,000 "LB" where the position of the first quote is 11? I mean it worked magically with the entire column regardless of where the first quote was. I just want to be able to replicate the magic in the future.

 

[Tetra201]

Let's work with some examples, say looking for the first letter "a":

Example 1: California. =FIND("a","California") returns 2. =MID("California",2,99) returns "alifornia". =SUBSTITUTE("alifornia","a","") returns "liforni"

Example 2: Montana. =FIND("a","Montana") returns 5. =MID("Montana",5,99) returns "ana". =SUBSTITUTE("ana","a","") returns "n"

 

[azzzy]

Let's work with some examples, say looking for the first letter "a":

Example 1: California. =FIND("a","California") returns 2. =MID("California",2,99) returns "alifornia". =SUBSTITUTE("alifornia","a","") returns "liforni"

Example 2: Montana. =FIND("a","Montana") returns 5. =MID("Montana",5,99) returns "ana". =SUBSTITUTE("ana","a","") returns "n"

Ah! I see now! I got confused with the number 7 in GET.CELL(7,INDIRECT("RC[-1]",FALSE)) which evidently had nothing to do with the position of the first quote. Thank you so much for the explanation!