numbers in concatenated columns not recognised

**Linden** · 08-25-2010, 03:12 AM

I'm merging 2 columns of numbers (no numbers are in the same row), which looks fine, but when I try to use this column in a pivot table, the numbers all show up as zero when I use 'sum' in the Field Settings. When I look at Format for the column of numbers, General is selected, changing it to any other setting (number, accounting, currency) makes no difference. It seems as though the concatenate function makes the numbers unrecognisable in the pivot table. Thank you for reading, thanks even more if you can help!

**JBeaucaire** · 08-25-2010, 07:42 AM

When you concatenate numbers, you get a text string. You have to do something mathematical on the text string to convert it back into a number.

A1 = 5
B1 = 3
C1 =A1&B1 (result 53, but it's a text string)

Better:
=(A1&B1)+0 (result 53 and it's a real number)

**Linden** · 08-25-2010, 10:53 AM

Fantastic, thank you so much. I spent hours trying to sort this out - I think your answer is one to remember because I've had similar problems before. Heartfelt thanks from 'across the pond'!

**JBeaucaire** · 08-25-2010, 11:23 AM

Other formula functions that will always result in text strings:

LEFT()
RIGHT()
MID()
CONCATENATE()
TEXT()
CHAR()
UPPER()
LOWER()
PROPER()
REPT()
REPLACE()
ASC()

...heck, here's a list that helps:
http://office.microsoft.com/en-us/ex...005203209.aspx

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.