Tuesday, February 18, 2014

yogi_Decouple Row By Row Set Of Strings In Column B And Present Instead Numbers Associated With The Corresponding Strings

                                         Google Spreadsheet   Post  #1536
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-18-2014
post by schnittke79 (https://productforums.google.com/forum/#!mydiscussions/docs/a2u2L0UFDZQ)
processing cell values that are space delimited
i have "Ax Be Ct Dd" or "Rd Xd Sr Ts Td Dq Gd3" or "W#2 T3 E4 Dw Tx" in many cells in column B.

In other words, some short strings that are space-delimited.

On another sheet I have a table that's called "Translation table". 
Each such short string has a corresponding number value.

I need an existing function or some script to output those numbers (also space delimited) in another column.

So, for the first example: Ax=1, Be=2, Ct=3, Dd=4.
I want the cell with the formula that looks like =ConvertToNumber(B2) to come back with "1 2 3 4" in that cell. etc

I know that I would be using VLOOKUP to find those values in my TranslationTable. But how do I iterate over short strings in one cell and then output similarly?

Is there an existing way?