Converting characters to text strings

[mogens]

To easily generate texts, I would like to translate text codes into text
strings.

If A translates into "Text A", B into "Text B" etc, then I would like
these texts entered into a column to be shown like this:

A "Text A"
ABC "Text A, Text B, Text C"
BC "Text B, Text C"

The triggy issue for me is that I do not know how many characters there
are in each string to be translated. It could be 1 or it could be 10.

Mogens

 

[Guest]

Use VLOOKUP

Make a table with the code in the first column and the full text in the
second.

Then the VLOOKUP function can return the full text if supplied the code.

 

[Jay]

One way, but it's not particularly elegant. (The example assumes A1 as
being your first cell, and 1 being your fist 'working' row)

Let's say Column A has your 'characters' which could be anything from
one letter (A) to 7 letters (ABCDEFG).

• Insert 7 blank columns to the right of column A, and hide them
• Select the data cells in column A and Choose Data » Text to columns
• Choose 'Fixed Width' & click between and after each letter in the
'Data Preview) (find your largest text string first)
• Choose $B$1 for your destination

Then input this formula into cell I1:

=IF(B1<>"","Text ","")&B1&IF(C1<>"",", Text ","")&C1&IF(D1<>"",", Text
","")&D1&IF(E1<>"",", Text ","")&E1&IF(F1<>"",", Text
","")&F1&IF(G1<>"",", Text "&G1,"")&IF(H1<>"",", Text "&H1,"")

Copy the formula down and you should have what you were after.

HTH

Jason

Any probs, just re-post

 

[Jay]

If you want a formula'd way of splitting the text across the 7 columns,
rather than having to use text to columns, put the following formula into
cell B1 and copy across to H1:

=MID($A1,COLUMN()-1,1)

This will split your (up to 7) character string into separate cells, and the
IF formula from my last post will add the Text wording you're after.

Let me know how you get on

Jay

 

[mogens]

If you want a formula'd way of splitting the text across the 7 columns,
rather than having to use text to columns, put the following formula into
cell B1 and copy across to H1:

=MID($A1,COLUMN()-1,1)

This will split your (up to 7) character string into separate cells, and the
IF formula from my last post will add the Text wording you're after.

Let me know how you get on

Jay

Thanks for the suggestion Jay. This will work for me, at least at the
present level of product coding. When these expands I might have to
rethink the way this is done.

Cheers /Mogens