Count characters within a cell to the left of "/"

  • Thread starter Thread starter robert
  • Start date Start date
R

robert

I have text cells with text separated by the slash character. For example:
northern/coast What formula will tell me how many characters are to the left
of the "/" character? The answer to my example would be 8.
 
I have text cells with text separated by the slash character. For example:
northern/coast What formula will tell me how many characters are to the left
of the "/" character? The answer to my example would be 8.
Click to expand...


Try this formula:

=FIND("/",A1)-1

Hope this helps / Lars-Åke
 
Lars-Åke Aspelin said:
Try this formula:

=FIND("/",A1)-1

Hope this helps / Lars-Åke
Click to expand...


When you clever guys give us 'newusers' solutions to our problems I
like to look at those solutions to see if I can work out the logic
behind them, and thus maybe learn a little about Excel.
In this case I found myself asking "So what formula will Robert need
if he wants to know how many characters are to the *right* of the "/"
character? The answer's not at all obvious - not to me, anyway!
I guess it will start with FIND - but what follows?

TIA of any reply,
 
When you clever guys give us 'newusers' solutions to our problems I
like to look at those solutions to see if I can work out the logic
behind them, and thus maybe learn a little about Excel.
In this case I found myself asking "So what formula will Robert need
if he wants to know how many characters are to the *right* of the "/"
character? The answer's not at all obvious - not to me, anyway!
I guess it will start with FIND - but what follows?

TIA of any reply,
Click to expand...


FIND is used to find the position of a string within another string.
If you combine that with the LEN() function that returns the length of
a string you can calculate the number of characters to the right.

Hope this help / Lars-Åke
 
Lars-Åke Aspelin said:
FIND is used to find the position of a string within another string.
If you combine that with the LEN() function that returns the length of
a string you can calculate the number of characters to the right.

Hope this helps / Lars-Åke
Click to expand...


Thanks for your reply - and so quick! Aren't you going to spell it out
for me? I'll struggle all week with the LEN() function!
If I live to a million years I'll never get to the bottom of this
marvellous Excel!

BW's - and a Happy Christmas, when it arrives.[/QUOTE]
 
Thanks for your reply - and so quick! Aren't you going to spell it out
for me? I'll struggle all week with the LEN() function!
If I live to a million years I'll never get to the bottom of this
marvellous Excel!

BW's - and a Happy Christmas, when it arrives.
Click to expand...


Of course I can spell it out, just didn't want to spoil the fun for
you of finding out yourself after doing some experimenting:

1) Put your text, containing the some characters, "/", and some more
characters in cell A1.
2) Put the formula =FIND("/",A1) in cell A2. What does it show?
3) Put the formula =LEN(A1) in cell A3, What does it show?
4) How many characters are there to the right of the "/" in cell A1?
5) Find out a way to combine the formulas in A2 and A3 to get the
result you want.

Hope this helps / Lars-Åke
 
Lars-Åke Aspelin said:
Of course I can spell it out, just didn't want to spoil the fun for
you of finding out yourself after doing some experimenting:

1) Put your text, containing the some characters, "/", and some more
characters in cell A1.
2) Put the formula =FIND("/",A1) in cell A2. What does it show?
3) Put the formula =LEN(A1) in cell A3, What does it show?
4) How many characters are there to the right of the "/" in cell A1?
5) Find out a way to combine the formulas in A2 and A3 to get the
result you want.

Hope this helps / Lars-Åke
Click to expand...


Thanks again! Your reply gives me summat to occupy a wet Sunday
afternoon here in Buckinghamshire, UK..
I'll not trouble you further!
 
Back
Top