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.

 

[phamENG]

You'll probably have to program a logic loop to get the results you're looking for. As far as I know, the only formulas similar to what you want are the LEFT(), MID(), and RIGHT() formulas. If you don't want to do VBA, you could do a series of IF() or IFERROR() statements in conjunction with the position formulas. So do a series of IFERROR(RIGHT(A1,i)/1,i-1) formulas in B1 so that, once the number of characters on the right includes a letter, it'll return one less. Do a total count of the characters minus that number as your number of characters (x) in a LEFT(A1,x) formula to give you the result you want.

 

[3DDave]

I don't know of a formula that can iterate - the attached sheet checks the first 10 characters and delivers the component in column B.

 

[Kenja824]

Thanks for the ideas, but not exactly what I need.

Im surprised there isnt a formula that will simply extract text from a cell and not numbers. So it would take a cell that has 7BA358 and return BA. Then it would be simple to find the location of the BA and use a LEFT formula.

Currently, I scanned through all of the cells and found there were three different letters being used in this particular file and did the following formula...

=LEFT(F780,IFERROR(FIND("H",F780,1),IFERROR(FIND("K",F780,1),IFERROR(FIND("L",F780,1),"."))))

Which is basically what phamENG was saying. The problem is not all files I work with will have those same letters and some will have more than 3. It would be nice to have a specific formula I could enter and find the first numbers and letters no matter what numbers or letters they use.

3DDave, I might have to look into yours further. Maybe I can find a way to combine them all into one formula. Maybe not but it might be worth looking into.

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.

 

[phamENG]

Simply working with formulas you probably won't find a great solution. VBA is likely going to be the best solution in Excel.

 

[Kenja824]

I know and agree. The problem with VBA is I suck at it. I have been trying to learn VB Coding for years when it comes to NX Journals and I just cant seem to pick it up. My whole life, anything I have put my mind to, I could figure out, but VB just doesnt sink in. In fact, VB code, or VBA for excel as I understand it, is the reason for my quote under my signature. lol It bothers me how much I am always asking people for journals. lol

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.

 

[Denial]

This problem is a classic case for VBA, via a User Defined Function.[ ] Dive into it.[ ] You'll open an entire new world.

 

[Denial]

Couldn't help myself.[ ] Put the following code into a VBA "Module".

Option Explicit
Option Base 1

Public Function Kenja(InpStr As String) As String
'
'  Removes trailing digits from a string that comprises
'  a mixture of UPPERCASE letters and digits.
'
'  Elaborations might be required for strings that contain:
'      Lower case letters
'      Only letters
'      Characters that are neither digits not letters.
'
Dim L As Long           'Length of input string
Dim I As Long           'General purpose integer
Dim Result As String
'
L = Len(InpStr)
If L <= 0 Then
    Kenja = ""
    Exit Function
End If
'
'  Loop backwards from the input string's end until hit an uppercase letter.
'
For I = L To 1 Step -1
    If Mid(InpStr, I, 1) >= "A" And Mid(InpStr, I, 1) <= "Z" Then
        Kenja = Left(InpStr, I)
        Exit Function
    End If
Next I
'
' Input string contains no letters.
'
Kenja = InpStr
'
End Function

 

[1503-44]

Here you go.
Big formula, but all in one cell.
Not even Google knows how to do this,

https://www.dropbox.com/s/uvhin7zcnfbzf9w/GET LEFT ALPHANUMERICS.xlsx?dl=0

 

[phamENG]

Ken - I feel the same way. That's why I decided to just let VBA be that mysterious thing that I'll never master, but can use google to bend (mostly) to my will when the situation requires it.

Denial and 1503-44 - nicely done.

 

[3DDave]

I don't have an interest in a dropbox account. Can that file be attached here?

 

[IDS]

Congratulations to 1503-44, but the VBA route is really much easier to use, and easier to understand what it does and how it does it.

I was going to have a go at the VBA, but as Denial beat me to it, I will just say:

- The "Dim Result as string" is not used so can be deleted (picky, I know)
- I was going to correct it to work with lowercase letters, but I see the comparisons in VBA (and on the spreadsheet) are not case sensitive, so it works with with lowercase as it is.
- For the non VBA lovers out there, to use Denial's code:
Open a new spreadsheet (or an existing one you want to use the function in) and save with a chosen name.
Press Alt-F11 to open the VBA editor.
Right-click on VBAProject(spreadsheet name) in the list of open files on the left, and Insert-Module
Copy Denial's code and paste it in the new module.
That's all.

You can then use the Kenja function, just like any built-in Excel function.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Function get_left(X As String) As String
Dim i As Integer
For i = Len(X) To 1 Step -1
If (InStr("0123456789", Mid(X, i, 1))) = 0 Then
get_left = Left(X, i)
Exit Function
End If
Next i
End Function

This is more flexible as the characters of interest are just put into a list.

It would be an alternative to make the list of characters an argument for the function.

Integer should be fine as Integer handles up to 2,147,483,647 which is the maximum length for a VBA string.
This does not need the test for length as null strings return null strings.

 

[IDS]

Integer should be fine as Integer handles up to 2,147,483,647 which is the maximum length for a VBA string.

Not very important, but Longs are actually faster than Integers, and have three less characters to type, so I always use Longs, which saves thinking about it.

More importantly the Integer limits are -32768 to +32767. 2,147,483,647 is the limit for a Long.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

If Integer are that short then I've been duped. Not like Microsoft make their documentation so useful.

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/integer-data-type

but no doubt that is because Microsoft has different limits for the exact same named type for VB vs VBA.

Spolsky - this is your fault. /s

Edited - nice try but it takes 3 keystrokes to type Integer and 4 to type Long.

 

[IDS]

3DDAve - Sorry, I should know better than to use the first hit from an Internet search without checking it.

The Microsoft documentation (after much searching) confirmed your number was right.

It's still easier to always use Longs and not worry about it though


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

Edited also: Using the shortcuts, they both take 3.

 

[IDS]

3DDave - At least we agree on Dropbox, but if you follow the link you can download to your local drive without signing in.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Catch my edit.

I didn't just look at the first one; it was the first bunch of them. VBA Integer - Now I wonder if it's just overzealousness of Google to PageRank the most linked to pages or a subtle dig at MS; what am I saying, MS has done enough double-speak badly documented on-line material.

I'm more worried/impressed that you know Longs are faster. It's likely my age and too many languages. Every time I work on VBA it takes a while to get the different function names than used on the worksheets and from C, et al. I so want to use debug.print sizeof(Integer); in VBA.

This works:
Function junk() As Integer
junk = 2 ^ 15 - 1
End Function

This fails.
Function junk() As Integer
junk = 2 ^ 15
End Function

I tried dropbox - it told me to log in. But what I see is that (I use NoScript) only 2 of three scripts were presented for approval the first page load. The third one must do some other thing and be loaded by one of the first two. Without that it sends me to the login page. I see that I go to the site, allow two scripts; it reloads; then I have to go back and allow a third one. Sigh. (The script apparently saves a cookie.)

Anyway, now downloaded through the phalanx of scripts, looking at the formula - Holy Cow. I guess it's for when regex is too simple.

When I use the shortcut it requires LON<tab> vs IN<tab>; perhaps another MS upgrade? I'm at Office 2010.

 

[IDS]

I thought I had posted something on my blog about the speed of Integers and Longs. I couldn't find it, but I did find:

Related to 24*60*30 in VBA raising an error:
"In short, the problem is that the result data type defaults to the largest type of the values being operated on, so if they are all integers the result is an integer, and since the largest value for an integer is 32767 you get an overflow."

Full article:

https://newtonexcelbach.com/2019/10/01/a-problem-with-integers-in-vba/

I just checked nothing had changed:

Function IntCheck()
IntCheck = 24 * 60 * 30
End Function

returns an error, but

Function IntCheck()
IntCheck = 24 * 60 * CLng(30)
End Function

returns 43200


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Now - great puzzlement; as if I've had a stroke and can no longer read.

This part of that formula:

=SUM(LEN("3L481") - LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) shows the answer "4"

It uses the SUM() but has only one argument, or so it appears. (I've replaced the cell reference of interest with example text to follow along better.)

Then I look at the contents: =LEN("3L481") shows the answer (expected) "5"
Then I look at the next item =LEN(SUBSTITUTE("3L481",{"0","1","2","3","4","5","6","7","8","9"},"")) which also answers "5" though I expected the answer to be 1 as the substitutions should reduce all the digits to nulls and leave only the one "L" for a length of one.

edit: Nope - of course, =SUBSTITUTE("ABC",{"A","B"},"") returns "BC"

So the SUM() of 5 - 5 = 4.

However, on it's own =SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},"") produces "3L481" which is correctly reported by LEN().

Finally =SUM(LEN(SUBSTITUTE("3L481", {"0","1","2","3","4","5","6","7","8","9"},""))) produces the answer "46".

All in Office 2010, so if this all works differently in some new version I would love to see the bug reports or I'll need a brain scan for a major bleed or blockage.