A20.6 Excel: Extract value from string - LUPMISManual

A20.5 Excel: Extract Value from String

Level of expertise required for this Chapter: Expert

You have a long table, where the entry clerk has combined a string with a number into one field. See the example below, for import to LUPMIS the business classes and descriptions are merged in one column.

In LUPMIS, you need the numeric class (of the business) in column AA.

Just insert following formula, which picks the numbers from column AC

=LOOKUP(99^99,--("0"&MID(AC1,MIN(SEARCH(0,1,2,3,4,5,6,7,8,9,AC1&"0123456789")),ROW($1:$10000))))

and copy it down to all records.

Of course, if the source is not column AC, you have to modify it in the formula (replace AC with the correct column).

You will need the value instead of the formula, so you might also have to copy - paste special - value, before you import into LUPMIS.