[Closed] Sorry, my turn for an Excel query

what's the maximum number of 1s you might have at the start of the string? And is the string fixed length or variable length?

0000000000000000000000000000000001111111111111111110000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

that is an example of the string I was looking at. The function I was using stripped away the 0s at the front by looking for the first 1. This was all fine until someone started adding 2s and 3s etc to the strings. I was hoping to change the code to find the first instance that is not a zero.

how to find the first instance in a string that is not 1

CAn you explain? Do you mean the first instance of a number that is not 1?

so in this example:

2323222002002302002030200323000[b]1[/b]1111000000

is the position of the bold "1" what you're after?

There may be a simpler way to do it, but you could do it with regular expressions pattern matching. Regualr expressions are in VB, so you would have to write a function to do the pattern matching and call if from you formula.

I think I get it now, you want to find the pos of maybe a 1 or a 2 or 3 in something like this:

00000000000[b]2[/b]2313123231213

feenster, yes the first instance of a number that is not 1 (well, 0 actually)

stoner, i'd be after the first non-zero character, so the bold here for example 00000[b]2[/b]32322200200230200203020032300011111000000

I was hoping to avoid VB as the spreadsheet is pretty much done and dusted, it just need this slight tweak

=find(substitute(A1, 0, ""), A1, 1)

will replace all 0 with nothing and then find where whats left ocurs in the string

EDIT hang on, i need to refine that

EDIT - try: =FIND(LEFT(SUBSTITUTE(A1, 0, ""), 1), A1, 1)

The requirment seems to be to return the index of the first charachater that matches the pattern [1-9].

This is the bread and butter of regular expressions.

Like I say, a simple Regular expression in simple VB function would do it, if you can't find some clever use of built in excel formula functions.

Stoner, oh yeah, very clever.

Except that will return the postion of ANY non-zero character, not just [1-9], but if that's ok, then this works....

i'd be after the first non-zero character

except that will return the postion of ANY non-zero character, not just [1-9]

thats what I was assuming

he he you need to look into to RegEx I doubt Excel can do what you want. You can install RegEx and creat a custom VBA function.

Do you know VBA?
this should get you started....

Function RegExpSubstitute(ReplaceIn, _
ReplaceWhat As String, ReplaceWith As String)
#If Not LateBind Then
Dim RE As RegExp
Set RE = New RegExp
#Else
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = ReplaceWhat
RE.Global = True
RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)
End Function

Function RegExpFind(FindIn, FindWhat As String, _
Optional IgnoreCase As Boolean = False)
Dim i As Long
#If Not LateBind Then
Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
Set RE = New RegExp
#Else
Dim RE As Object, allMatches As Object, aMatch As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = FindWhat
RE.IgnoreCase = IgnoreCase
RE.Global = True
Set allMatches = RE.Execute(FindIn)
ReDim rslt(0 To allMatches.Count - 1)
For i = 0 To allMatches.Count - 1
rslt(i) = allMatches(i).Value
Next i
RegExpFind = rslt
End Function

he he you need to look into to RegEx I doubt Excel can do what you want. You can install RegEx and creat a custom VBA function.

pffft.

Bloody code monkey's always trying to justify more code rather than find a neat solution 😉

pffft.

Bloody code monkey's always trying to justify more code rather than find a neat solution

Ahh but more code in this situation will present a neater formula...
This may help...
[url] http://regexlib.com/CheatSheet.aspx [/url]

cheers everyone - Stoner's crakced it....again 😀

=FIND(LEFT(SUBSTITUTE(A1, 0, ""), 1), A1, 1)

is perfectly neat, without having to put your elastoplast specs on and write a small novel in VBA 😉

here is the final function if anyone needs to strip leading zeros off a stupidly long binary code

=RIGHT(A8,(LEN(A8)+1-(FIND(LEFT(SUBSTITUTE(A8, 0, ""), 1), A8, 1))))

excel can be versatile although VBA is moreso, it's just I have to revise each time I want to use it as I don't use it often enough.

Ta again

woohoo! That's 3-0 this week.

Im so good at this I should do it as a job or something. Oh wait, I do 😉

Albert Einstein: "everything should be made as simple as possible, but no simpler"

the basist at the jazz gig I was at last night was quoting another bassist from the 30s:

"Simple is easy, but easy isnt always simple"

but easy isnt always simple

too right, it's just taking me this long to figure out exactly what you code did, I am impressed.

Not sure if I'm missing something here but if you're just trying to strip the leading zeros from a binary number held in a text format can you not just use the VALUE() instead of SUBSTITUTE() which would replace every 0 in the string (as I understand it?).

I'll get me coat...........

JimmyB almost except it was only leading zeros that I had to strip away. Any zeros within or after non-zeros I had to keep

He's on to something.

VALUE turns text to a number, so '000001023' becomes 1023, because leading zeros are redundant in a number.

Sorry, think I worded that badly, even I can't work out what I meant.

So if you only want to strip the leading zeros and leave the rest intact then the simple use of VALUE() will do this. 00001111000 will be come 1111000 etc. If wanted in text then can be converted back using TEXT().

If you want to still find the first non zero character in the string then either VALUE() or SUBSTITUTE() in Stoners formula will work beautifully.

Yep, VALUE works. Sorry stoner, you've been trumped on simplicity.

...what feenster said before I refreshed my screen...... 😀

Actually the way the original problem was explained was a bit misleading. The actual requirement was to strip leading zeros, not to find the index of the first non-zero character. If that had been said I'm sure stoner would have been the first ro suggest VALUE 😉

OP, suggest you use:

=TEXT(VALUE(A8),A8)

much more readable and understandable than:

=RIGHT(A8,(LEN(A8)+1-(FIND(LEFT(SUBSTITUTE(A8, 0, ""), 1), A8, 1))))

when you come to look at this again in 6 months

hmmmm, interesting. I think I have to keep it as text because that was just a middle stage. Once zeros stripped away each character then needs separating and putting in an individual cell. I just gave the Value method a try and all looked promising however it insists on representing it in a scientic format. Even if you change the format to number when you come to split the code up it takes 555000etc etc as 5.55E+ etc etc. I think it needs to remain as text. I almost had to kick myself 😉

=TEXT(VALUE(A8),A8)

will give you what you want won't it?

actually it won't the format argument of the TEXT is a bit weird.... 😳

Hmmm

almost

but as you say, it is doing something weird

try =TEXT(VALUE(A1),0)

the format_text is obscure but using 0 there seems to work.

0 appears to be the General format so probably what the cell was originally.

Value is simpler indeed - but as you say, only strips if you're trying to clean zeros. Substitute is needed if you want to clean non-zeros, which as you say, you asked in the first post looking for 1s.

EDIT: BTW to keep the format when using Value, use:

=TEXT(VALUE(A1), "#")

Careful. Looks like there's some rounding going on somewhere for me.

=TEXT(VALUE(A1), "#") on

00000232322200200230200203020032300011111000000

gives:

232322200200230000000000000000000000000000

doesn't seem to work with either 0 or "#". It's not recognising a non zero after any zeros in the sequence. So 00011100020 which would become 11100020 then becomes 11100000.

I possibly should have put the whole reasoning behind the original query up but it would have been a bugger to type out and as it was a slight tweak I thought taking the bit that needed tweaking was sensible. Sorry if that was not the case.

Anyway, for now Stoner I'll be using the original solution....which is still much better than VBA 🙂

Looks like it's magically storing it in Scientific format but displaying it in 'normal' format, hence the missing information.

Quite interesting, must be a way around this but what are the chances that it will end up far more complicated than Stoners solution! 😯

good point re the "#" thing - bluddy floating point calcs 🙂

So - in the end you still have to use Substitute 🙂

[url= http://office.microsoft.com/en-us/excel/HP051992911033.aspx ]Excel can't handle number bigger than 15 digits[/url]. What happens with the VALUE function is that the text is changed to a number, which then breaches this limit, so all digits after the 15th are changed to zero.

So, VALUE is no good.

you see what you need is to use some other form of counting system than binary. like, maybe decimal or something - someone might even have invented it already though 😉