Excel formula help please 9

[Kenja824]

Cell A2 will have numbers with one or two letters mixed in. The letters are always together.

3L481
27H429
9SB75
etc...

In A1 I would like to place a formula that will return the numbers in the beginning along with the letters, but not the numbers after the letters.

3L
27H
9SB

I never know for sure what letters or numbers will be used.

I cant seem to find any examples of this.


Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[1503-44]

3DDave, Are you good? How do you live without Dropbox? I hate sharing via Bluetooth.

At first I also wanted to do this with a UDF, but it seemed Kenja specifically wanted to avoid VBA.

I did have to use the "string replace" a lot to build the one cell formula when I packed the formulas from the 4 or 5 cells I had into one. The end result is totally incomprehensible, but its all in one cell. It seems the best the built-in functions can do is give a count of the quantity of numbers or alphas in a string.

If the letters are not always grouped, it won't return the correct string. Checking for that error condition using built-in functions isn't going to be easy.

It would have been a ton easier if there was a built-in function that could distinguish between alphas and numerics on its own, rather than by having to use the search for a/n specific characters.

Strangely enough, maybe something similar to the substitute error, searching with Max instead of Min returns a number that is too large by an amount equal to the number of characters in the search string.

I think that computer has Office 2013.

 

[3DDave]

I use the following which uploads to Eng Tips.

People usually forget to click the button to add it to the posting.

 

[3DDave]

=SUM(LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) => 46 seems to be:

evaluate each character one time against each item in the match list, which is 50; then subtracts 4 matches to get 46.

When I use multiple digits, such as "00", "99", "19" => 18.

When I use a character, such as "A3" => 19, because 20 checks - 1 match.

For truly strange results:
[pre]
=SUM(100+LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => 1090
=SUM(100-LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => 910
=SUM(10- LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => 10
=SUM(1- LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => -80
=SUM(0- LEN(SUBSTITUTE("0123456789", {"0","1","2","3","4","5","6","7","8","9"},""))) => -90[/pre]

 

[Denial]

3DDave,

WRT your 14Jul21@07:07 puzzle, I think I know how Excel is getting that result even though I don't quite understand WHY is takes the approach that it uses. (I am using Excel 2010, like you.)

If I select a single cell and enter the formula
=SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},"")
I get "3L481" like you get.
However if I select a 1x10 array of cells and enter the same expression as an Array Function I get
3L481[ ] 3L48[ ] 3L481[ ] L481[ ] 3L81[ ] 3L481[ ] 3L481[ ] 3L481[ ] 3L41[ ] 3L481

If I now do a SUM(LEN(....)) of that 1x10 array, again as an Array Formula, I get the same 46 result as you are getting.

So your non-Array formula
=LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},""))
would return the length of the first element in the array, ie 5.
And your
=SUM(LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},"")))
returns the sum of the lengths of the ten strings
3L481[ ] 3L48[ ] 3L481[ ] L481[ ] 3L81[ ] 3L481[ ] 3L481[ ] 3L481[ ] 3L41[ ] and[ ] 3L481.

Clear as mud?
Me too!

 

[Kenja824]

Got to work today and found quite the discussion going on in this thread. lol



Denial and 1503-44;

Thankyou both for your help. There are others here who will use these, so both will be used depending on who it is.

Im equally impressed with both.



phamENG;
If you can bend them to your needs, you are already leaving me in the dust. I cant seem to even really get the basics and I have been trying for a few years now. I have several VB codes that allow someone to pick a face, or a solid body, or whatever in NX. I cant figure out how to change it to picking a point. lol No matter what it is, it seems like the entire language changes. I have tried and tried and code just doesnt seem to sink in. Very frustrating.



IDS;
Wish I would have read further. I spent a little time trying to remember how to add a module. Been a while. Finally remembered, then found your instructions. lol



As for the rest of the conversation, I need to keep my sanity a little longer, so I am not going to try and understand it all.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[1503-44]

Kenja,

Record some simple macros, open them and step through their progression. You will soon enough come to understand VBA.

 

[electricpete]

If I can give one piece of advice on VBA, it is simply to anticipate that you will need it again and again in the future, and put in place a structure that will help you in that journey.

Here's my structure. I have a word file (collapsible outline format). If I find a new vba routine that I'm interested in or I just developed I might put it in there. My favorite ones migrate to the top of the file. When I go back to that file, sometimes I just grab a particular routine. Other times I go back just to try to find what is the particular syntax to do something... I go back to the favorite routine that I remember includes that syntax.

You might want to put a link to this thread in that word file so you can easily come back and look at what was discussed above.

Here are 3 of my own favorite projects that you may or may not be interested in:

1 - Do you want to create a formula in a cell which will show the actual formula (rather than result) of another cell. Grab this one:

Function GetFormula(Cell)
   GetFormula = Cell.Formula
End Function

Paste it into your module. Use it in your spreadsheet =getformula(A3) as a feature to help document the logic of your spreadsheet (along with named ranges, another great tool for readability).

2 - Did you ever wish excel had a "switch" function. You can get one by copying the code here: thread770-281108. Once you get used to using a switch function you'll go back and grab it every time you need it.


3 - I also have vba code that highlights the current row and column as I move my cursor around in a spreadsheet. It helps me identify the row and column headers more readily (minor benefit). The bigger benefit is that if I have two windows open on my screen trying to translate information between a spreadsheet and my other application, I can keep track of exactly where I am in the spreadsheet (excel's built in current-cell highlighting disappears when you make another window active). That's actually the one honestly more useful to me than any fancy numerical calculation because unfortunately a lot of what I do is much less exciting just translating info in and out of spreadsheets. (Let me know if you want that one)


=====================================
(2B)+(2B)' ?

 

[IRstuff]

I second the notion of using the macro recorder; most often, I record the sequence of things I want the macro to do and then slog through the recording to generalize it.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[1503-44]

Yeah. Nothing better than eating one tiny "byte" at a time, immediately relevant to what you want to accomplish at the moment.

 

[MintJulep]

My variation, inspired by 3DDave's VBA function.

Public Function Kenja(S As String) As String

Dim Countdown As Long

Countdown = Len(S)

Do Until InStr("0123456789", Mid(S, Countdown, 1)) = 0
    Countdown = Countdown - 1
    Kenja = Left(S, Countdown)
Loop

End Function

No need to have a test inside a loop when you can have the test as the Until condition.
Also does away with the need for the Exit Function statement.

 

[MintJulep]

And in case the input is a number.

Public Function Kenja(S As Variant) As String

Dim Countdown As Long

If Not IsNumeric(S) Then

    Countdown = Len(S)
    
    Do Until InStr("0123456789", Mid(S, Countdown, 1)) = 0
        Countdown = Countdown - 1
        Kenja = Left(S, Countdown)
    Loop
    
Else
    Kenja = ""
End If

End Function

 

[1503-44]

Do I detect a Z80 programmer?

Seriously.
I like it.

 

[MintJulep]

Maybe once during a lab course in university, long ago.

I just like minimalist code.

 

[Denial]

MintJulep

Your function possibly does the wrong thing if the input string does not end with a digit.
(Kenja's description of his problem does not specifically address this situation, but my reading of it is that he would want the string returned unchanged rather than a null result.)

 

[MintJulep]

It's implied that they will always be numbers after letters.

 

[1503-44]

It is defined so.
My cell formula, as I stated, will not work, if the letters are not grouped as well.

(We built to specs!)

Yeah. 8k memory does like tiniest code possible.
I think its a good habit.

 

[IDS]

Pete:
1. You copy your VBA code into Word? Weird (but each to their own)
2. Have you seen the new Switch function in the latest Office versions? (just the subscription version so far I think).
3. I'd be interested to see your row/column highlighter code.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

1503-44 - do any of the sub-elements work for you as I have described and if so, how did you know that they would work in the exact combination you used? Without the use of the SUM() wrapper they do not produce usable answers for me.

 

[SkipVought]

Here's my structure.

The code I want to save, reuse, reference, I put in my PERSONAL.XLSB file. Then it's just ready to use and reference, always.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Please forgive me, but I find it amusing how much of a discussion this has turned into. To be honest, I read through it and dont understand a quarter of what is said. lol

To clarify, the cells info will always start with a number, have a letter or two (always together if two) and then end with a number. At least that is the only format I have ever seen so far. Now that I have made that statement, I am certain I will find a job that is different.

I do thank you all for your help.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.