preserving cell formatting inside a CONCATENATEd string

[Ron.Winkley]

Hi to All,

I have formatted cell C5 as follows: ##"F1"

I entered the number 1712 cell C5, which gives the desired result
1712F1

Now I want to CONCATENATE(C5,xx,xx,xx) to give result 1712F1 xx xx xx
but what I get is 1712 xx xx xx

Note that the formatting of cell C5 is variable and will change,
for example from ##"F1" to ##"P1", ##"F2" or to something else.

In each case I want the CONCATENATED string to correspond to
the present format of cell C5, for example 1712F2 xx xx xx

How can I achieve this variable format in my CONCATENATED string
to automatically correspond to the format of cell C5?

Thank you for your assistance,

RonW.

 

[Guest]

First enter this tiny UDF:

Function txet(r As Range) As String
Application.Volatile
txet = r.Text
End Function

this function return the visible contents of a cell. So if A1 displayed:
123hello
and the hello was supplied by format only, =txet(A1) would give the full
string.

to use it:

=CONCATENATE(txet(A1),"xxxx,xxxx")

 

[Bob Phillips]

Something like this

=TEXT(C5,"0""F1""")&"xx"&"yy"&"zz"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron.Winkley]

Thanks Gary, but there's a problem . . .

Function txet(r As Range) As String
Application.Volatile
txet = r.Text
End Function

I created the User Defined Function by copying and pasting the above
exactly as it is, and the function does not work, it just returns
#NAME?

Are you able to explain what is wrong?

Thank you very much . . .
Cheers,
RonW.
____________________________________________________________

 

[Gord Dibben]

To where did you copy/paste the UDF?

It should be pasted into a general module in your workbook.

Alt + F11 to open VBEditor.

CTRL + r to open Project Explorer Window.

Right-click on your workbook/project and Insert>Module.

Paste into that module.

Alt + q to go back to your worksheet.

Enter the formula as shown.


Gord Dibben MS Excel MVP

 

[Ron.Winkley]

Hey, you guys are brilliant!

Gord, your reply encouraged me to look at it again and I saw that the
UDF had been saved in Personal.xls The below function works!

=PERSONAL.XLS!txet(E6)

Problem solved. That's my first ever UDF - something new to me,
nice to see it working!

Thanks for everything . . .

Cheers,
RonW.

 

[Ron.Winkley]

Now this leads me to something else:

can I use the same

=PERSONAL.XLS!txet(E6)

or something similar to preserve my formatting using VLOOKUP?

example:
sheet1 has cells formatted as described, eg 1223P1
sheet2 is using VLOOKUP to extract data from these cells
I want VLOOKUP to preserve the same format as in sheet1

a little more help, please

Cheers,
RonW.

 

[Guest]

Hi Ron:

You can use the function in conjunction with VLOOKUP(). However, you need
to include a column in the lookup table that has txet entries. What will not
work is something like:

=txet(VLOOKUP(...))

That is because txet needs to know WHERE the value came from, not the value
itself. VLOOKUP() only gives the value.

 

[Ron.Winkley]

All OK now, I've already got it sorted - thanks again to all who
helped . . .

Cheers,
RonW.