Return max string length before special character within a cell

S

schinois

New Member
Joined
Oct 18, 2008
Messages
6
Hello,

I would like to find the Excel formula (not VBA if possible) to know the longest string before meeting special characters within a cell.

Where does it apply?

CELL A1 Content
The formula related to A2

CELL A2 Content
You have earnt:/n1000 points/n/nPress the cross button to enter the shop./n

What the formula should do?
I would like to return the max string length (len()) before the characters "/n" within a cell.

According to the Cell A2 content, it should return the value "41" since:

"Press the cross button to enter the shop." = 41 characters
"You have earnt:" = 15 characters
"1000 points" = 11 characters


If you can do it with a regular Excel formula, it would be more simple for me.
However, I understand that only some solutions can be done via VBA (I already have some running macros in the same sheet).

Thanks in advance for your help if someone knows ;)

Stephane
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
schinois said:
Hello,

I would like to find the Excel formula (not VBA if possible) to know the longest string before meeting special characters within a cell.

Where does it apply?

CELL A1 Content
The formula related to A2

CELL A2 Content
You have earnt:/n1000 points/n/nPress the cross button to enter the shop./n

What the formula should do?
I would like to return the max string length (len()) before the characters "/n" within a cell.

According to the Cell A2 content, it should return the value "41" since:

"Press the cross button to enter the shop." = 41 characters
"You have earnt:" = 15 characters
"1000 points" = 11 characters


If you can do it with a regular Excel formula, it would be more simple for me.
However, I understand that only some solutions can be done via VBA (I already have some running macros in the same sheet).

Thanks in advance for your help if someone knows ;)

Stephane
Click to expand...

Hi Stephane:

And how about the presence of /n and /n/n long before the 41st character in cell A2?
Please clarify.
Could you per chance mean to count the number of characters after the last occurrence of /n?
 
Upvote 0
Actually, that is my problem :) As you may know, these are line breaks.
I need to know what is the maximum amount of characters per line.

According to this information, I can tell others the maximum amount of characters per line. I'm doing localization for a video game, and that kind of information helps a lot ;)

Let me know if you need further more information,

Thanks Yogi,

Stephane
 
Upvote 0
schinois said:
Actually, that is my problem :) As you may know, these are line breaks.
I need to know what is the maximum amount of characters per line.

According to this information, I can tell others the maximum amount of characters per line. I'm doing localization for a video game, and that kind of information helps a lot ;)

Let me know if you need further more information,

Thanks Yogi,

Stephane
Click to expand...

Hi Stephane:

I did not get the answer to my question. However, I have a hunch you are looking for the following ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
AB
2You have earnt:/n1000 points/n/nPress the cross button to enter the shop./n41
Sheet8


</body></html>
 
Upvote 0
see the sheet below.
see the formulas in C2 to G2. it finds out the place of the successive "/n". the formulas are similar. you can type the formula in C2 and copy upto the time you get "#value". that means there are no more /n
now see the formula in C3 . as the llocation starts from 1 this is special formula
see D3 formula and copy it to E3, to F3 once cell before the 2nrow give error .

try on a few more strings with "/n" and give feedback.
Book1
ABCDEFG
1stringfirst/nsecondthirdfourthfith
2Youhaveearnt:/n1000points/n/nPressthecrossbuttontoentertheshop./n16293174#VALUE!
31511041
Sheet1
 
Upvote 0
Hi Stephane

Also, in B1:

=MAX(FIND("/n",RIGHT("n"&A1&"/n",4+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))-2)

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.
Copy down


<TABLE style="BORDER-TOP-WIDTH: 2px; BORDER-LEFT-WIDTH: 2px; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BACKGROUND: #fff; BORDER-BOTTOM-WIDTH: 2px; BORDER-BOTTOM-COLOR: #cccccc; BORDER-TOP-COLOR: #cccccc; FONT-FAMILY: Arial,Arial; BORDER-COLLAPSE: collapse; BORDER-RIGHT-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc" cellPadding=1 border=1><TBODY><TR><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">A</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888" width=30>B</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888" width=30>C</TH></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">You have earnt:/n1000 points/n/nPress the cross button to enter the shop./n</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">41</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">Where does it apply?/n/nCELL A1 Content/nThe formula related to A2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">25</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">3</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">"Press the cross button to enter the shop." = 41 characters/n"You have earnt:" = 15 characters/n"1000 points" = 11 characters</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">59</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">4</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">1/n23/n456/n7890/n1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">4</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">5</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: left; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">a/n/n/nbc</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">6</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=4>[Book1]Sheet1</TD></TR></TBODY></TABLE>
 
Upvote 0
Absolutely brilliant, PGC!!

It calculates Len(string) if the text does not contain /n.

So if the evaluation is to be done only for values which MUST contain /n and zero for non-'/n', the formula would be:
=IF(ISERROR(FIND("/n",A1)),0,MAX(FIND("/n",RIGHT("n"&A1&"/n",4+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))-2))

But this is just an aside (maybe wholly unnecessary for schinois) - the formula you built is awesome!

My rating is for the response.
 
Upvote 0
Thanks all for your replies :) Actually, I have to say that PGC thought about the others cases. So, PGC's formula is working great for me ;)

Thanks!

Stephane
 
Upvote 0
Stephane, I'm glad it helped.

Mala, thanks, you are right, it calculates Len(string) if the text does not contain /n. That's how I interpreted the question, the formula returns the length of the longest line in the text. If there's no newline (/n) then the text has only one line and the formula returns the length of the text.

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Return position character first appears in a text string from the right hand side
Replies
9
Views
725
Peter_SSs
Peter_SSs
N
  • Question
Dismissing special characters in a text string
2
Replies
17
Views
833
Peter_SSs
Peter_SSs
V
  • Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
356
kvsrinivasamurthy
K
A
  • Question
Determine Point of Cell Word Wrap of A String (max length of string permitted for a static sized cell)
Replies
5
Views
1K
Micron
M
P
  • Solved
validate a character string within a cell to a list and return the value found
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top