EXCEL 2003 right function not recognising <100

**Rayo** · 12-31-2012, 02:43 AM

hi
I am running a spreedsheet based around various reports.
each repot has it's own name & number which increases in increments of 1.
I use the mid function to as a calculator to alter report numbers.
this works fine after 100 but for numbers not of three digits it returns only 2
ie stop.001 returns stop.02(not stop.002)
the formula i use is
1. first cell A30 = stop."&"015" (this is a manual field)
2. second cell = "stop."&(RIGHT(A30,3)+1
can someone please explain what the problem is
thank in advance
Rayo

**HaHoBe** · 12-31-2012, 02:54 AM

Hi, Rayo,

maybe use

Please Login or Register  to view this content.

Ciao,
Holger

**Ace_XL** · 12-31-2012, 03:35 AM

Another way..

- Custom Cell Format (Ctrl+1) your original manually entered cell viz. A30 as well as other cells in your range as

Please Login or Register  to view this content.

- Simply enter number 15 in Cell a30
- In subsequent cell use

Please Login or Register  to view this content.

**Rayo** · 01-01-2013, 10:25 PM

Thanks Ciao
this works fine.
so this would mean EXCEL recognises these numbers as text and is returning a numbers as a text rather than numbers as a number.
is this correct?

**HaHoBe** · 01-02-2013, 12:54 AM

Hi, Rayo,

the Right-Function delivers Text (all Textfunctions do that by default) which Excel converts to and treats as a number when you add 1 to it (any other digit will do the same or if you multiply the outcome by 1). The Format just displays the number in the way you want it to appear.

HTH,
Holger