Tags: TEXT

Convert a text entry into its number equivalent

{0 Comments}

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1).

text Value
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 0

The objective is to generate the numeric code for text code of any length entered in a certain cell.  For example, a user will type a certain text code, say ABEJ and the expected result should be 1250.  For JABF, the result should be 0126.  The text entry and text length are both user determined.

With ABEJ, typed in cell D2, enter this array formula in cell E2

=TEXT(SUMPRODUCT((LOOKUP(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),$A$2:$A$11,$B$2:$B$11))*((10^(LEN(D2)-1-(ROW(INDIRECT("1:"&LEN(D2)))-1))))),REPT("0",LEN(D2)))

This formula can now be copied down for generating the numeric code for all text codes entered in column D.

Programmatically transfer data from master sheet to sub sheets with conditions

{43 Comments}

A user inputs raw material purchase data in a master sheet.  This data is entered by date and vendor.  One may want to view individual vendor sheets at a monthly level.  In the attached file, i have presented two solutions - a macro based one and a formula based one.

The data layout and instructions are very clearly mentioned in the this workbook.

You may also refer to the a similar article at the following link.

Determine weekday after factoring in user specified holidays

{0 Comments}

Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days).  When determining the ending date, the following two cases should be considered

Case 1

Holidays are:

1. Sundays only; and

2. User specified public holidays

Case 2

Holidays user specified public holidays only.  Saturdays and Sundays are working days

If the landing date is any of the two holidays, the result should be the next working day.

You may refer to my solution in this workbook.