Excel Formula Help

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

Celticshadow

Hi Everyone

I have six numbers and letters in a cell on a spreadsheet and I am using the
formula =RIGHT(F1,1) to obtain the first letter or number. However the
formula is not picking the first number or letter in the string in all cases.
Some of the cells are left blank.

Any help would be much appreciated.

Kind Regards

Celticshadow
 
.. Some of the cells are left blank

Probably these are trailing white spaces
which can be cleaned up using TRIM
Try it as: =RIGHT(TRIM(F1),1)

High-five? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
Hi Max

That works a treat. Many many thanks. May I ask what exactly trailing white
spaces are?

Kind Regards

Celticshadow
 
That works a treat. Many many thanks.
Good to hear. You're welcome.
.. what exactly trailing white spaces are?
Click to expand...
White spaces are those spaces that you enter with the spacebar. They are not
visible. Trailing just means located "after" as in after the visible
characters. You could also have leading white spaces as well, those in front
of the visible characters, albeit these are not of issue here. Trust that
clarifies.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
 
I have six numbers and letters in a cell on a
spreadsheet and I am using the formula =RIGHT(F1,1)
to obtain the first letter or number. However the
formula is not picking the first number or letter
in the string in all cases. Some of the cells are
left blank. Any help would be much appreciated.
Click to expand...

Without details about the ones that don't work and the ones that work,
we can only make guesses.

First, RIGHT would return the __last__ character, not the first one.
Use LEFT if you want the __first__ character.

Second, it is possible that you have trailing (or leading) spaces
after (before) the text that you see in the cell. There are a couple
ways to "see" this:

1. Enter the formula =">"&F1&"<" in some cell, and be sure there are
no leading and trailing spaces.

2. Enter the formula =LEN(F1) in some cell, and be sure that the
length is the same as the number of characters that you see.

If either approach shows unwanted leading or trailing spaces, there
are several ways to get rid of them, depending on how many cells are
affected.

The easiest approach is to manually edit the offending cells, and
delete the unwanted spaces.
 
Back
Top