preserving cell formatting inside a CONCATENATEd string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron.Winkley@gmail.com - 28 Oct 2007 03:37 GMT

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.

Reply to this Message

Gary''s Student - 28 Oct 2007 10:47 GMT

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")

Signature

Gary''s Student - gsnu2007a

> Hi to All,
>
[quoted text clipped - 18 lines]
>
> RonW.

Reply to this Message

Ron.Winkley@gmail.com - 29 Oct 2007 04:53 GMT

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.
____________________________________________________________

On Oct 28, 9:47 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> First enter this tiny UDF:
>
[quoted text clipped - 37 lines]
>
> > RonW.

Reply to this Message

Gord Dibben - 29 Oct 2007 05:15 GMT

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

>Thanks Gary, but there's a problem . . .
>
[quoted text clipped - 57 lines]
>>
>> > RonW.

Reply to this Message

Ron.Winkley@gmail.com - 29 Oct 2007 06:40 GMT

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.

> To where did you copy/paste the UDF?
>
[quoted text clipped - 75 lines]
>
> >> > RonW.

Reply to this Message

Ron.Winkley@gmail.com - 29 Oct 2007 07:09 GMT

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.

On Oct 29, 5:40 am, Ron.Wink...@gmail.com wrote:

> Hey, you guys are brilliant!
>
[quoted text clipped - 90 lines]
>
> > >> > RonW.

Reply to this Message

Gary''s Student - 29 Oct 2007 11:30 GMT

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.

Signature

Gary''s Student - gsnu200752

> Now this leads me to something else:
>
[quoted text clipped - 109 lines]
> >
> > > >> > RonW.

Reply to this Message

Ron.Winkley@gmail.com - 30 Oct 2007 01:22 GMT

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

Cheers,
RonW.

On Oct 29, 10:30 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Hi Ron:
>
[quoted text clipped - 123 lines]
>
> > > > >> > RonW.

Reply to this Message

Bob Phillips - 28 Oct 2007 11:15 GMT

Something like this

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

Signature

HTH

Bob

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

> Hi to All,
>
[quoted text clipped - 18 lines]
>
> RonW.

Reply to this Message