DVDAddin

English

Tiếng Việt

English

Tiếng Việt

Appearance

Recipes (common formulas) ​

Snippets that combine DVDAddin's UDFs with Excel's built-in functions for frequently occurring tasks. The full list of 47 functions is at UDF functions.

The examples below use a semicolon ; as the argument separator (Excel running on the Vietnamese regional format). If your machine uses the English (US) format, type a comma , instead of ;.

Text handling ​

Clean up text pasted from elsewhere ​

=TRIM(CLEAN(dvdUnDiacriticsVi(A1)))

→ Strips Vietnamese diacritics + removes non-printable characters + removes extra spaces. Commonly used to turn a work-item name into a file name for the records.

See dvdUnDiacriticsVi.

Rescue text typed in the wrong encoding ​

=dvdUniConvert(A1; "VNI")     → "Be6 to6ng"  becomes  "Bê tông"
=dvdUniConvert(A1; "Telex")   → "Bee toong"  becomes  "Bê tông"

Split elements out of a code string ​

A code in the form AG.11221-BT-M300:

=dvdTextSplitItem(A1; "-"; 2)     → "BT"
=dvdExtractElement(A1; 3; "-")    → "M300"

The difference: dvdTextSplitItem has an IgnoreEmpty argument for skipping empty elements, while dvdExtractElement keeps the positions as they are.

Count words, count occurrences ​

=dvdWordCount(A1)                   → number of words in the cell
=dvdCountOccurrences(A1; "D16")     → number of times "D16" appears in the string

Join task names by work item ​

=dvdConcatIF(", "; C6:C40; B6:B40; "Móng")

→ "Bê tông lót M100, Bê tông móng M300, Cốt thép móng". Omit the last two arguments to join the whole range. For several conditions use dvdConcatIFS or dvdJoinIFS.

Evaluate an expression written in a description line ​

A1 = "Móng M1: 2*3.5*0.8":

=dvdEvaluate(A1)     → 5.6

→ A hand-written quantity breakdown still yields a number; there is no need to retype the formula.

How the function reads the string: if there is a :, it takes only the part after the colon, cuts off everything from the = sign onwards, then drops all letters and keeps only digits together with the operators + - * / ^ ( ) . , (the letter x is read as multiplication, a comma is read as the decimal separator). If the result is 0 or cannot be computed, the cell returns an empty string.

Format a Vietnamese phone number ​

=TEXT(VALUE(SUBSTITUTE(A1; "+84"; "0")); "0000-000-000")

→ +84901234567 becomes 0901-234-567.

Change case, find-and-replace with regex ​

There is no UDF for these two jobs — DVDAddin does them with ribbon commands that write straight into the cells, so no helper column is needed:

  • DVD Addin → Text and Number → Case: Proper Case, UPPERCASE, SMART case (AI) (Ctrl+Shift+S).
  • DVD Addin → Text and Number → Text ops → Bulk find / replace… — three match modes (plain text / wildcard / regex), scope from the selection all the way to every open workbook, a Preview table for ticking each cell before replacing, and parameter sets that can be saved as presets.

Dates and calendar ​

Count working days excluding Vietnamese public holidays and Tết ​

Sheet Holidays, column A, holds the list of holidays:

=NETWORKDAYS(A1; B1; Holidays!$A$2:$A$30)

Report due date (the 5th working day of the following month) ​

=WORKDAY(EOMONTH(TODAY(); 0); 5; Holidays!$A$2:$A$30)

Lunar New Year milestones for the schedule ​

=dvdLunarToSolar(DATE(2026;1;1))     → 17/02/2026 (first day of Tết, year Bính Ngọ)
=dvdSolarToLunar(A1)                 → "10/07/2026 AL"

→ Get the first day of Tết as a solar date, then add or subtract to build the non-working-day column for the schedule. Format the cell as Date so that dvdLunarToSolar displays correctly.

Dates written out in words for minutes ​

=dvdLDate(A1; "Hà Nội"; 0)     → "Hà Nội, ngày ... tháng ... năm ..."
=dvdLTimeDate(B1; A1; 2)       → time + date, bilingual (0 = VN, 1 = EN, 2 = bilingual)

Money and numbers ​

Numbers in words for invoices and payment certificates ​

=dvdVnd(SUM(C2:C100))     → "Một trăm hai mươi lăm triệu đồng."
=dvdVnd(F42; 1)           → "Bằng chữ: Một triệu, hai trăm năm mươi nghìn đồng."
=dvdUsd(5000)             → "Five thousand dollars only"

→ The grand-total cell at the end of a contract / payment dossier spells itself out in words.

VND currency format ​

This is a Number Format, not a function: Ctrl+1 → Custom → #,##0" đ". Use a format rather than a function so that the cell stays a number and can still be summed.

USD/VND exchange rate ​

=DVDFx("USD"; "VND")                     → latest exchange rate
=DVDFx("USD"; "VND"; "2026-06-30")       → rate at the payment period cut-off

→ The function runs asynchronously: the first time round the cell shows #N/A waiting... and then updates itself. Results are cached for one hour per currency pair.

Round to a multiple of 1000 ​

=MROUND(A1; 1000)     → 1.234.567 becomes 1.235.000

To wrap ROUND around a whole batch of formulas that already exist in the sheet, use the Rounding Func command — it adds or removes the function in bulk instead of you editing cell by cell.

Interpolate a norm by haul distance ​

=dvdNoiSuy(5; 10; 120000; 150000; 7)     → 132000

→ The transport unit price at a distance of 7 km, interpolated between the 5 km and 10 km points.

Lookup and data ranges ​

Get the most recent record when a code repeats ​

=dvdELookup(A6; $B$5:$B$300; $F$5:$F$300)

→ The quantity of the last match — correct for a diary table that grows over time. To take the value at the largest date regardless of row order, use a plain Excel formula:

=INDEX(History!B:B; MATCH(MAX(History!A:A); History!A:A; 0))

Get every matching value, not just the first ​

=dvdMVLookup($H$3; $B$5:$B$500; 4; $J$4:$J$40)

→ The first matching value appears in the formula cell, the remaining values are written into J4:J40. Compare with VLOOKUP, which returns only the first result.

To return several columns at once use dvdMCLookup — the formula cell shows Done and the data lands in the target range.

Look up across every sheet ​

=dvdLookupAllSheets(A6; 2; 6)

→ Searches for the task code in A6 in column B of every visible sheet and returns the column F value. It skips the sheet that holds the formula and any hidden sheets.

Two-way lookup by row and column header ​

=dvdTableLookup("D16"; "Cấp bền B22.5"; $A$5:$H$30)

Reverse lookup (from name to code) ​

=dvdXlookup("Đào đất"; $B$6:$B$500; $A$6:$A$500; "Không có"; "Lỗi"; 0)

→ It has built-in replacement values for the not-found case and the error case, so there is no need to wrap it in IFERROR. Pure Excel version: =INDEX(A:A; MATCH("Đào đất"; B:B; 0)).

Sum the visible cells only (after filtering) ​

=dvdSumVisible($F$6:$F$500)

→ Compare with SUM (which counts hidden rows too) and SUBTOTAL(9; ...) (which only drops rows filtered out by AutoFilter — manually hidden rows are still counted). dvdSumVisible checks the hidden state of both the row and the column containing the cell, so it skips all three ways of hiding a row — AutoFilter, manual hiding and dvdAutoHide — as well as any hidden columns.

Sum and count by cell background colour ​

=dvdSumIfColor($F$6:$F$500; $H$3)       → sums the cells with the same colour as H3
=dvdCountIfColor($F$6:$F$500; $H$3)     → counts the cells with the same colour as H3

Filter a table on a condition in one column ​

=dvdFilter2DArray($A$5:$F$500; 6; ">0"; TRUE)      → only the rows with a quantity > 0
=dvdFilter2DArray($A$5:$F$500; 2; "BT*"; TRUE)     → the task codes starting with "BT"

→ Returns a two-dimensional array: Excel 365/2021 spills it automatically, older versions need Ctrl+Shift+Enter.

Unique list ​

=dvdUnique($B$6:$B$500)                       → a one-column array of the unique values
=dvdUnique2DArray($A$5:$F$500; 2; TRUE)       → keeps the first row of each code, all columns
=dvdUniqueV($B$6:$B$500; $H$7:$H$40)          → for Excel 2016/2019 without dynamic arrays

Auto-hide rows whose quantity is zero ​

=dvdAutoHide($A$6:$A$500; $F$6:$F$500; "<>0"; $A$6:$A$500; "Ẩn dòng KL 0")

→ Keeps the rows whose quantity is not 0, hides the rest and renumbers them consecutively. Put the formula in a cell outside the Target range, otherwise it hides itself along with them.

Top 10 work items by value ​

=TAKE(SORT(DATA!A2:C1000; 3; -1); 10)

→ SORT and TAKE are built into Excel 365/2021, no UDF needed.

Construction ​

Rebar weight ​

=dvdSteelWeight(16; 20)      → 369.72 kg (20 D16 bars, 11.7 m long)
=dvdSteelWeight(8; 250)      → 250 kg (D8 coil rebar entered by weight)

→ For D ≤ 8 the function returns the quantity value unchanged, because coil rebar is entered in kg; from D9 upwards it reads quantity as a number of bars and multiplies by the standard 11.7 m bar.

Count rebar by diameter ​

=COUNTIF(D:D; "D16") + COUNTIF(D:D; "Ø16")

→ Counts both notations commonly used in bar schedules.

Total quantities by structural member group ​

The table has column A (member code) and column B (quantity):

=SUMIF(A:A; "C-*"; B:B)     → all columns
=SUMIF(A:A; "D-*"; B:B)     → all beams
=SUMIF(A:A; "S-*"; B:B)     → all slabs

Rebar anchorage / lap length ​

The anchorage factor follows the applicable standard and the working conditions of the bar; the example below uses a factor of 40D:

=ROUND(40 * VALUE(MID(A1; 2; 2)) / 1000; 2)

→ A1 = "D16" → 40 × 16 / 1000 = 0.64 m. Replace 40 with your own factor.

Estimate how many rebar bars to buy ​

The table has column B (bar length, m) and column C (count), 5% wastage, 11.7 m bars:

=CEILING(SUMPRODUCT(B6:B500; C6:C500) * 1.05 / 11.7; 1)

→ This is only an estimate based on a flat wastage rate. For the real figure use Rebar Cut — a genuine bar-nesting algorithm that produces a cutting diagram and the offcut left on each bar. For other materials there are Bar Cut 1D and Panel Cut 2D.

Automatic quantity breakdown notes ​

=dvdExplain(F6; ; 0; 2)          → "2.40=3*0.8"
=dvdExplainE(F6:F20; ; 0; 3)     → "5+3.2+7.8=16.000"

→ dvdExplain writes result = expression, dvdExplainE writes expression = result (which suits the breakdown tables of as-built records). Turn on Wrap Text for the formula cell when the breakdown spans many cells.

QR codes for member tags ​

=dvdQR(A6)
=dvdQR("https://hoso.congtrinh.vn/bb/" & A6)

→ The formula cell displays nothing; the real output is a QR image inserted into the cell. The default character set is UTF-8, so content with Vietnamese accents still scans correctly; to use the Japanese character set, pass "Shift_JIS" as the second argument (the string then loses its accents).

Site photos for the acceptance annex ​

=dvdPic("Anh\" & A6; TRUE; 6)

→ The relative path is resolved from the folder holding the Excel file; the image scales to fit the cell, leaving a 6 px border. When exporting PDFs in bulk, use the Batch print command so that the images are refreshed before export.

Colour a zoning layout plan by schedule ​

=dvdColorShapes($H$6:$I$40; "Mat bang"; 0.2)

→ H:I are the two columns holding shape names and RGB colour codes; the last argument is transparency, 0..1.

Spreadsheet automation ​

An index column that numbers itself as rows are added ​

Cell A2 and below:

=IF(B2=""; ""; COUNTA($B$2:B2))

→ The number only advances when column B (work-item name) holds data.

Pass / Fail check-mark cell ​

=dvdSymbol(IF(D6="Đạt"; 2; 3))

→ ☑ when Đạt (pass), ☒ when not. 1 = empty box.

Highlight invalid codes with Conditional Formatting ​

CF rule: =AND(B2<>""; ISERROR(VLOOKUP(B2; BangGia!A:A; 1; FALSE)))

→ Turns cell B red if the task code is NOT in the price list.

Clickable email and phone cells ​

=HYPERLINK("mailto:" & A1; A1)
=HYPERLINK("tel:" & A1; A1)

A bilingual column for records sent to a foreign consultant ​

=dvdTranslate(B6; "vi"; "en")

→ Requires an internet connection. Use it on individual cells; dragging the formula down hundreds of rows at once is slow and easily blocked — for a large table, use the Translate command.

Multi-step formulas ​

The nth occurrence ​

Find the 3rd occurrence of "Hà Nội" in column A:

=INDEX(A:A; SMALL(IF(A:A="Hà Nội"; ROW(A:A)); 3))

(Enter with Ctrl+Shift+Enter on Excel versions without dynamic arrays.)

A chart title that updates with the month ​

=CONCATENATE("Doanh thu tháng "; TEXT(TODAY(); "MM/yyyy"))

AI inside a formula cell ​

Ask AI to explain an unfamiliar formula ​

=dvdAIExplain(F6; TRUE)

→ Requires a Gemini API key entered in Preferences → the AI Assistant section. The function runs asynchronously. For an interactive dialog instead of a formula, use Explain formula (Coach); when a cell is reporting #N/A or #VALUE!, use Fix formula error.

Released under DVDAddin License.

Copyright © 2026 Đặng Văn Đằng — dvdpm.net