The Excel-2019 tag is used for referencing the spreadsheet component of the Microsoft Office 2019 suite released 1 Oct 2018 (Commercial preview released 27 April 2018). The version independent Tag is "excel". If it is about an Excel formula or worksheet function, then tag it "worksheet-function". If the question pertains to VBA code, the "vba" tag should be used in addition to this one.
Questions tagged [excel-2019]
183 questions
6
votes
4 answers
How to detect Office 2019 programmatically?
We are used to detect the currently installed Office version by getting the full version (e.g. from the file info of an installed Office executable) and map the mayor version to a friendly name, like 15 maps to "Office 2013", 16 to "Office…
bgx
- 751
- 10
- 14
5
votes
1 answer
Diacritic normalization in excel formula, without vba
How to replace diacritics (accented characters) with corresponding alphabetical character with regular formula: =SUBSTITUTE(A1,"é","e"), without VBA, for large range of uppercase and lowercase diacritics like this:
áàȧäǎāãåąⱥấầắằǡǻǟẫẵảȁȃẩẳạḁậặăâ…
Losai
- 329
- 2
- 9
3
votes
1 answer
Add two Characters Objects together so as to concatenate their text but retain formats from each
I am adding the contents of cells to a shape object. The contents are all text, but each cell may have different formatting. I would like to be able to preserve this formatting when adding the content of the cells to the shape, so that a bold cell…
High Plains Grifter
- 1,357
- 1
- 12
- 36
3
votes
1 answer
VBA: Global Constant Strings
I'm working with special characters for mathematics in Excel.
(Exponents⁴, GRΣΣK, and other misc Unicode characters.)
I'd like to store commonly-used Unicode strings as global constants, since that would probably be significantly faster (and less…
Giffyguy
- 20,378
- 34
- 97
- 168
2
votes
2 answers
Is there a function for Excel 2019 like index match or vlookup to find a value in a row added cross sheet?
I am running a report on Excel 2019 which has 2 sheets, and I want to automate the value in the field called Status in the sheet2 with the new status on sheet1 where a new row was added for the same item.
Ex:
thank you.
I tried index match, but a…
David Knight
- 49
- 5
2
votes
1 answer
Combine Two Array Formulas
I have in (D2) very dirty string:
Ⱦ[ḫ]îs ɨs síṁƥḽẽ ~s?tring $with Ḑiă{cr}îtíc#s ẵɲd Ṧɏ<ṃƀǿⱡs. /123
To clean this I use two great formulas (Thanks @Jvdv)
First - Cleaning string from unwanted…
Losai
- 329
- 2
- 9
2
votes
1 answer
Remove matching strings by multiple substrings using excel formula
How to remove or replace familiar words in string, by list of familiar stubs of that words?
List contain ~40 stubs (substrings). With all of this I expect substitute hundreds matching words.
I'm interested in formula solution because I already know…
Losai
- 329
- 2
- 9
2
votes
1 answer
Replace all non-alphanumeric characters, including wildcards
I take this beautiful formula from JvdV answer:
=TRIM(CONCAT(IF(ISNUMBER(SEARCH(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")),MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)," ")))
This formula replace any…
Losai
- 329
- 2
- 9
2
votes
1 answer
SUBSTITUTE or REPLACE all non-alphanumeric characters
This array formula (CTRL+SHIFT+ENTER) trim any non-alphanumeric characters:
{=TEXTJOIN("";1;MID(D2;ROW(INDIRECT("1:"&LEN(D2)))*IFERROR(SEARCH(MID(D2;ROW(INDIRECT("1:"&LEN(D2)));1);"-./ 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0;LEN(D2)+1);1))}
But I…
Losai
- 329
- 2
- 9
2
votes
1 answer
Format Cell in a column based on previous cell in column - Excel 2019
I know there are other answered questions like this but they are either outdated or dont explain how to get to the menu to enter things in. I am a novice Excel user and need a little help in getting cells in a column to be formatted based on the…
Sash
- 23
- 8
2
votes
1 answer
How do I automatically subtract time data of previous row from current row in Excel?
Here's what I want to achieve:
In the example, I manually subtracted the value of the previous cell from the cell.
For example:
For cell C2, I used =A2-A1
For cell C3, I used =A3-A2
For cell C4, I used =A4-A3
And so on.
How do I make this process…
Um9vdAo
- 53
- 2
- 9
2
votes
2 answers
An issue when generating text with national symbols using VBA in Excel 2019
I am using some VBA add-in to generate text from numbers, that text contains national Lithuanian, German or any other national symbols. This used to work fine with the previous versions of Excel so far, but the system has now been upgraded from…
Arturas M
- 4,120
- 18
- 50
- 80
2
votes
1 answer
Is OFFSET actually slow or just volatile?
I have a table in Excel that often uses OFFSET to get one column relative to another one. This is necessary to allow me to dynamically resize the table.
So for example in cell D5 of a MyTable, I have a formula like this - where D$2 is the first row…
Greedo
- 4,967
- 2
- 30
- 78
2
votes
1 answer
Output a 2D array formula as a formatted string
I have an array formula in a single cell that outputs some 2D array:
{={"a","b","c"; 1, 2, 3}}
(more complicated of course but that's the last step if I step through)
At the moment I am wrapping this result to return a formatted string as…
Greedo
- 4,967
- 2
- 30
- 78
2
votes
3 answers
Count Blank Cells Until Value is Found
How can I find a count of blank cells until a value is found in a row?
I did some searching and found that I could use either
COUNTBLANK with INDIRECT or COUNTIF or MATCH with INDEX
But couldn't get it to work on any of them...
The formula should go…
ggmkp
- 665
- 3
- 16
- 27