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.

 

[electricpete]

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.

1. You copy your VBA code into Word? Weird (but each to their own)

Haha, yeah I can see how it sounds weird. In the distant past I had problems with special characters from word messing up vba but that hasn't happened in a long time (I think the paste into vba editor now converts word's oddball characters into simpler text). I use word outline view for flexibility in all my notetaking. You can build hierarchical notes to whatever level indentation you want, collapse/expand them to whatever level you want, move them around when collapsed etc. If you want to search, expand all and search. It's easy to see and organize a lot of info that way imo (maybe it's just because that's the way I've always done it for last 20 years). The part where it's cumbersome is sharing info with others because if they don't view it in outline view then it can look very strange, so I often print my word outline to pdf to share it.

I’ve never taken advantage of personal.xlsb but I’ll have to look at that.

2. Have you seen the new Switch function in the latest Office versions? (just the subscription version so far I think).

Whoops, yeah I have that available to me too. UDF no longer needed for that one!

3. I'd be interested to see your row/column highlighter code.

[ul]
[li]For the benefit of other readers (Doug already knows), code below goes into the particular worksheet vba area rather than into a module.[/li]
[li]It could no doubt be improved for readability or style. You could use only one set of row/col variables, which are updated in between clearing the "last" selection and highlighting the current "this" selection, rather than using separate "last" and "this" variables like shown below. Or if you want to keep both sets of variables then you could move initialization of thisRow and thisCol to the beginning and update of lastRow and lastCol to the end. Maybe thisRow and thisCol should initialize based on Target instead of Selection. I'm not sure which is most elegant (open to comment), but it works so I'm happy[/li]
[/ul]

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Current Cell row/column highlighter
' update 07152021
' Usage note - if for some reason a cell remains highlighted
'     ... which is not the current cell, then fix it by
'     ... clicking on that cell and then clicking on any other cell

Static lastRow, lastColumn ' Holds the value from last call to sub (previous cell):

If lastColumn <> "" Then ' Clear color from the last cell:
    With Columns(lastColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(lastRow).Interior
        .ColorIndex = xlNone
    End With
End If

thisRow = Selection.Row  ' Update variables to current cell:
thisColumn = Selection.Column
lastRow = thisRow
lastColumn = thisColumn

With Columns(thisColumn).Interior  ' Color highlight the current cell:
    .ColorIndex = 6 ' yellow (vbYellow doesn't work)
    .Pattern = xlSolid
End With
With Rows(thisRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

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

 

[SkipVought]

(TB)+(TB)' CONJESTION

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

 

[SkipVought]

Just a technical nit picky suggestion.

I'd use Target rather than Selection.

I like your code. Have a star.

Never used Static before. Learned something.

Any reason for using ThisRow and ThisColumn? Couldn't it be...

lastRow = Target.Row  ' Update variables to current cell:
lastColumn = Target.Column

With Columns(lastColumn).Interior  ' Color highlight the current cell:
    [b].Color = vbYellow[/b]
    .Pattern = xlSolid
End With
With Rows(lastRow).Interior
    [b].Color = vbYellow[/b]
    .Pattern = xlSolid
End With

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

 

[1503-44]

3DDave,
I used SUM because I always do that when I have arrays and mixed type as arguments.
It accepts constants, ranges, named ranges, arrays, or cell references in any order and also ignores some errors returned by subfunctions that might be in the arguments, like it doesn't try to add text in one cell in what otherwise is a range of numbers.
Trying to add text to numbers with the + operator fails with the #VALUE! error, but SUM works.

What is returned by SUM(SUBSTITUTE())

SUM(SUBSTITUTE) RETURNED VALUE = LEN(STRING) x COUNT(ARRAY OF VALUES) - COUNT(FOUND IN STRING)
CELLS(C6).VALUE = "3LB481"
SUM(LEN(SUBSTITUTE(C6,{"X","P"},""))) = 24 when 0 of 2 array values is found in C6
SUM(LEN(SUBSTITUTE(C6,{"L","X"},""))) = 23 when 1 of 2 array values is found in C6
SUM(LEN(SUBSTITUTE(C6,{"L","B"},""))) = 22 when 2 of 2 array values is found in C6
SUM(LEN(SUBSTITUTE(C6,{"K","Z","X"},""))) = 36 when 0 of 3 array values is found in C6
SUM(LEN(SUBSTITUTE(C6,{"L","B","X"}))) = 34 when 2 of 3 array values is found in C6

 

[IFRs]

This was (is) a GREAT thread. I learned a lot (I have been looking forward to the day I get to use "SWITCH").

Keep it going!!!

 

[3DDave]

1503-44, that's fine that you found that out. Where did you find that out?

edit: I had already deduced the reason for the number. What I don't know is why Microsoft doesn't say anything about this in their documentation.

 

[Denial]

1503-44 (and others), with advance apologies for my inability speak proper Excelese.

As I tried to explain in my earlier (14Jul21@08:30) post, these seemingly strange results come about as a result of Excel's "confusion" as to whether to carry out the calculations in "array mode" or "non-array mode".

When I attempted to replicate your 15Jul21@19:26 final results I got (with "3LB481" in cell C6):[tt]
SUM(LEN(SUBSTITUTE(C6,{"X","P"},""))) = 12
SUM(LEN(SUBSTITUTE(C6,{"L","X"},""))) = 11
SUM(LEN(SUBSTITUTE(C6,{"L","B"},""))) = 10
SUM(LEN(SUBSTITUTE(C6,{"K","Z","X"},""))) = 18
SUM(LEN(SUBSTITUTE(C6,{"L","B","X"},""))) = 16[/tt]
where I first had to correct your fifth one to include the "" argument.
I cannot explain how your answers come to be different from these, because these seem to me to be correct given how I now understand my Excel2010 to be doing the calculations.[ ] (I suspect you might have cut&pasted from the wrong place.)

I then replaced the {squiggly bracket} character sequences with explicit ranges of cells, where I had put the elements of your character sequences into successive cells.[ ] This gave me the following results:[tt]
SUM(LEN(SUBSTITUTE($C$6,G1:H1,""))) = 6
SUM(LEN(SUBSTITUTE($C$6,G2:H2,""))) = 5
SUM(LEN(SUBSTITUTE($C$6,G3:H3,""))) = 5
SUM(LEN(SUBSTITUTE($C$6,G4:I4,""))) = 6
SUM(LEN(SUBSTITUTE($C$6,G5:I5,""))) = 5[/tt]

My conclusion from all this?[ ] The use of the {squiggly bracket} character sequences in the formulae causes Excel2010 to perform at least part of its internal calculations in "array mode".[ ] This conclusion is reinforced by the fact that if I enter my above "explicitly ranged" formulae as Array Formulae, they give me the same results as I got with the {squiggly bracket} character sequences (12,11,10,18,16).

 

[electricpete]

Any reason for using ThisRow and ThisColumn? Couldn't it be...

That cleans up my confusion with using vbYellow...I have to use it with color, not colorindex.
I agree with you the number of variables can be reduced, there are lots of ways to go.
Now that you make me think about it some more, we don't have to remember the color from the previous cell to clear it, we could just as well clear color on the whole sheet. (sure it wipes out all colors you may have put in there... but the old one did that too, just on a smaller scale).

So here's a new shorter version that also works. There are no extra variables.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Current cell row/col highlighter
' update 07152021a - this version wipes out all colors
' Usage note - if for some reason a cell remains highlighted
'     ... which is not the current cell, then fix it by
'     ... clicking on that cell and then clicking on any other cell

Target.Parent.Cells.Interior.Color = xlNone  ' wipes out all colors in sheet, including previous highlight

With Columns(Target.Column).Interior  ' Color highlight the current cell:
    .Color = vbYellow
    .Pattern = xlSolid
End With
With Rows(Target.Row).Interior
    .Color = vbYellow
    .Pattern = xlSolid
End With

End Sub

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

 

[3DDave]

Does this work with Conditional Formatting?

Fight, fight, fight!

I'm hoping for a Conditional Formatting knockout.

 

[1503-44]

1503-44, that's fine that you found that out. Where did you find that out?

Do you mean where I found out about SUM's superpowers??? It was back in the 90's.
My brother was a CPA at Warner Bros. (He met Tom Cruise on Top Gun.) He used Excel a lot, but didn't know anything about VBA, so I did a simple program for him to automate some tax filing he was doing at the time. When I was explaining what I did, he noticed that I used the plus operator to sum a group of cells in series and suggested I use SUM(A1:A20) instead. I looked more closely at what SUM could do. SUMPRODUCT is similar, but at times gives different error notices than SUM. I have not looked into SUMPRODUCT, as I've been happy as a clam just using SUM ever since then. Where? I was in Saudi at the time.

Better yet, maybe you don't know this, apology if you do, but you can access all of Excel's built-in functions through VBA by using the "APPLICATION" reference. I dont remember how or where I found that out. I was using VB6 at the time and doing a lot of data entry and result display with Excel through DDE methods, eventually using ActiveX controls I had developed that could be pasted onto an Excel worksheet based GUI to communicate with VB6. Finally my VB6 prog could send commands and receive messages to/from XL and a 3rd party hydraulics program I use, all from the XL GUI. Then MS abandoned VB6 for VB.net. Thank You MS!

 

[1503-44]

Denial, Sorry. I was copying and pasting from my spreadsheet to here. Might be an error there, or two...

 

[3DDave]

Yes, that's what SUM() is designed to do. It's not obvious that it is intended to iterate the entire contents with an impromptu array inside a SUBSTITUTE() function appearing somewhere in those contents. It iterates constants at the same time. But it doesn't always do it. It seems like it only does it when SUBSTITUTE() is used. For example, =SUM(1+SUM({1,2,3,4})) does not iterate.

I'll accept that you don't recall how this hack came to be.

 

[1503-44]

Oh, combining SUM and SUBSTITUTE. Might have seen something I saw in "Excel for Scientists and Engineers"? A reference I was using a lot around that time, but I can't be sure. If it only iterates with SUBSTITUTE, I didnt know that, search Google for both those terms together and maybe some documentation will show up.

 

[3DDave]

I guess I'm not $80 curious enough to see if Excel for Scientists and Engineers mentions it.

None of the nearly 100 websites I've looked at makes any such mention, but you did say "maybe." Maybe one of them has the winning lottery numbers for next week LOL.

Asking Abe Books turns up at least a dozen books by various authors and publishers with either that title or a very similar one. It's a wild goose chase.

 

[1503-44]

Winning lottery numbers for next week...
Let me think about that one.

 

[SkipVought]

3DDave, the question box,

I like that! I, too, am sort of looking for "the theory of everything" sort of, to SUM it up.

SUM (SUMPRODUCT) is on the way.

You had asked,

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

Well this intrigued me, too. It seems the SUBSTITUTE function, due to the 10 element array therein, returns 10 elements, the LENth of which are {5 4 5 4 4 5 5 5 4 5} based on the first argument, and those 10 values are summed.

In my doodle sheet I created a 10 element structured table for my 10 digits and a 26 element ST for the alphabet. That seems to work eliminating the need for a literal array.

I never downloaded from the dropbox, so I don't have the total solution.

I also saw that I can use SUM() instead of SUMIF, SUMIFS, COUNTIF, COUNTIFS.

BTW, for Conditional Formatting you need values not just a cell selection. Hence a VBA solution via the SelectionChange Event. Native Excel spreadsheet features feature no such feature. (Somewhat like: How would you describe a misgiving about a booking at a native American resort? A reservation reservation reservation.)



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

 

[1503-44]

"It seems like it only does it when SUBSTITUTE() is used."
Then is it not SUBSTITUTE that is doing the actual iteration work? SUM is just keeping track of it all.

Reservation.Reservation.Reservation
Cute.

 

[SkipVought]

The iteration is the result of the {array} (literal or referenced) and SUM sums the results of what's performed on the array.

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

 

[yakpol]

Here's a solution without using VBA, just formulas. It works if the number of digits right of the last letter is limited. In the presented formula it's assumed 5, but can be increased if needed. The formula in B1 returns the number of digits past the last symbol, the formula in C1 returns desired string.

[tt]A1=27H429
B1=IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,5))),5,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,4))),4,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,3))),3,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,2))),2,IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,1))),1,0)))))
C1=LEFT(A1,LEN(A1)-B1)[/tt]

Hope it helps.

 

[3DDave]

That array is not a supported or mentioned feature in Excel documentation.

This result is only seen with that specific combination of SUM ( SUBSTITUTE ( {} ). There is no documentation that suggests SUBSTITUTE should or could perform an iteration)

yakpol - your method only functions for a fixed number of characters, but the use of "pre" formatting goes the extra bit and spoils the width of the comment field so it is necessary to scroll to read replies.