Trying to display number and text in same cell

[Jay]

Hi,

I have a basic question which I still can't resolve even after finding the
specific section on the subject in the Excel 2002 help file (Grrr!).

I want to enter a number in a cell, and have it treated by Excel as a
number, and after the number in the same cell, write free form text
appropriate for that particular number alone. If I do that for 3 rows for
example, I want the ability to add the numbers in the resulting column, such
as the following:

cell A1 says: $5 value of John's cars
cell A-2 says: $2 value of Sue's stock
cell A-3 says: $7 value of Bill's computer
cell A-4 says: $14

Presumably cell A-4's formula would be =sum(a1:a3). Presently the
formatting for cells A1 through A4 is "currency" to two decimal places,
showing the $ symbol. I can get the cells to add without the text but not
with the text. I even tried putting the text in quotes hoping the formula
in A4's cell would ignore the text, but the formula didn't recognize the
numbers alone, and add them, disregarding the text in quotes.

There was also a reference I found in a newsgroup post using the @ symbol,
and so I tried $5 @value of John's cars but that didn't work
either. I think I have to "create" a custom category, and then format cells
A-1 through A-3 with this custom category, but I have no idea what the
string should be. TIA. Jay

 

[Norman Harker]

Hi Jay!

Try:
=SUM(--MID(A1:A3,2,FIND(" ",A1:A3)-1))
Entered as an array by pressing and holding down Ctrl+Shift and then
pressing Enter.

The data is in string form and we have to parse out the numbers. You
could use a helper column an use the formula:

=MID(A1,2,FIND(" ",A1)-1)

You then sum the results.

We use the array formula to avoid the need for a helper column. Within
an array, the MID function returns needs to be coerced from text to a
number and that is what the -- does.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

Norman,

I appreciate your trying to help me. Alas, I failed (got #Value! in the
cell which totals the numbers). I'm no doubt missing something simple but
for now, I'm going to kludge around the problem by putting all my notes in
comments, and have the comments "show" all the time.

It seems to me this is such a basic feature that Excel should have available
in a much simpler way than the approaches you suggested. Thanks again for
your advice, which I will save for a later "re-try". Jay

 

[CLR]

Format A1, Custom, as $####.00 "value of John's cars"
Format A2, Custom, as $####.00 "value of Sue's stock"
Format A2, Custom, as $####.00 "value of Bill's computer"
Format A4, for Currency

Vaya con Dios,
Chuck, CABGx3

 

[Jay]

Chuck,

I definitely feel your suggestion is exactly the simple approach I'm looking
for; still even so, I'm too thick today to simulate this simple 4 cell
example on a "test" Excel XP worksheet on my PC. If you ever have the time,
I would be grateful if you could e-mail me a worksheet with only A-1 to A-4
filled in that I could look at? I think if I saw it, the "lightbulb"
(hopefully more than 20 watts<g>) would go on. Jay

P.S. Can I choose to create a custom format such as:

$####.00 " " (I put a space between the quote marks)

so that anything I have in the cell receiving the custom format that's in
quotes is considered text? Having to compose a special custom format
definition involving actually typing in the very text for each cell's
contemplated text, as part of the definition, would not be user friendly.

(e-mail address removed)

 

[Max]

Another option to play around with, instead of using Comments ?

Put in A1: = 5 + N("value of John's cars")
Format A1 as currency

Repeat above construct with different values / text in A2 and A3

In A4: =SUM(A1:A3)
will return the total

 

[Jay]

Max,

It works! Thanks very much for this alternative suggestion.

I'm spoiled. With Chuck's help, I also now see how his approach works too.
I'm "rich"!! Newsgroups are great. Jay

 

[Jay]

Chuck,

Thanks for your fast help on the solution below. I instantly got it (a
picture's worth 1000 words). Now everything works fine. This "newuser" is
grateful.

Curiosity is going to get the best of me, I know, and I'll no doubt try to
master Norman's approach in the future. Today, I'm one happy camper. Take
care. jay

 

[Norman Harker]

Hi Jay!

That #VALUE! is probably because you didn't enter as an array.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

Norman,

The Eagle has landed!!!! eureka!!

Thanks for following up with that reminder. I "thought" I had entered the
formula as an array, but I guess not.

With your help (and Max and Chuck) I am 3 for 3 today (feel like Reggie
Jackson must have when he hit 3 home runs in one World Series game, well
almost<g>). Newsgroups like this are invaluable. Now the only thing is
Norman, even now, your formula doesn't restore the hour I somehow lost last
night<G>. Seriously, thanx for sticking with me. over and out. Jay

 

[Max]

You're welcome, Jay !
Thanks for the feedback.

 

[Norman Harker]

Hi Jay!

Re: "Now the only thing is Norman, even now, your formula doesn't
restore the hour I somehow lost last night<G>"

That's very easily sorted out. Move to Australia where we put our
clocks back last week and you'll get the hour you lost back plus one
more.

Or

Just change your System clock! Why work on the same time as everyone
else. This extra daylight only causes the curtains to fade.

Keep posting or you'll miss gems like that!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[CLR]

Thats one of the neat things about Excel...........there's usually several
ways of solving the same problem..........and this wonderful NG is a fine
place to see several solutions and choose the one that fits your situation
and skill level the best............glad you got it working, and thanks for
the thanks.............

Vaya con Dios,
Chuck, CABGx3