Insert 2 values as a custom format in Excel.

[alforber183]

Hi,

I am trying to insert 2 seperate values within the same cell with a custom format, eg;

Value 1 ranges from 0 - 999
Value 2 ranges from 0 - 9999

I want to be able to type 12(value 1) 567(value 2)and for it to desplay as

"tek" [Value 1] "tips [value 2]

"tek 12 tips 567" for example

I tried - "tek" ### "tips" ####

But that will not display 12 567(with space) with the text, but will display 121567(no space) as

tek 1 tips 2567

value 1 has been split with value 2.

Is it possible to set a custom cell format with to seperate numbers? I want both value 1 & 2 to be within ranges 0 - 999 and 0 - 9999 respectivly. If so how do i go about it.

Thanks

 

[Lilliabeth]

It is not possible to have 2 numbers in a single cell.

Is your question that you want Excel to display something like "tips" whenever you type a space in a cell?

--Lilliabeth

 

[SkipVought]

Hi,
[tt]
A1: 12
A2: 567
A3: ="tek "&A1&" tips "&A2
[/tt]
"tek 12 tips 567" for example


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

That your custom format works at all is a great tribute to Excel's flexibility! There is a fundamental difference between 12 567 with a space, and without.
With a space, Excel interprets it as text, so custom number formats become irrelevant. Without a space, it interprets it as a number, and Lilliabeth's point is true: you can only have one number in a cell. Your format asks it to place the last 4 digits after the second piece of text, so that is why it splits the "two" numbers incorrectly.

If you always type a leading zero for your "second" number when it is less than 4 digits, your conditional format will actually work, with the proviso that you are really only typing in a single integer and displaying it in a very strange way.

As always, Skip's approach is the professional one!