Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Get column index in Excel Table
  • Conditional Formatting Rules in Excel
  • How to sum a total in multiple Excel tables
  • How to Create Area Chart in Excel
  • How To Sort One Column or Multiple Columns in Excel

References

  • Lookup entire row in Excel
  • How to get last column number in range in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to use Excel VLOOKUP Function
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100

Split numbers from units of measure in Excel

by

To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text.

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt.

Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, numbers in this format are considered to be text inside of Excel,  and you won’t be able to perform any math operations on such values.

Worked Example:   How to Remove Leading Spaces in Excel

Formula

=MAX(ISNUMBER(VALUE(MID(A1,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1

Explanation

 

In the example shown, the formula in C5 is:

=MAX(ISNUMBER(VALUE(MID(B5,{1,2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8,9})+1

This formula uses the MID function to extract the first 9 values in B5, one character at a time. The result is an array like this:

Worked Example:   Split dimensions into two parts in Excel Worksheet

{“8″,”0″,”v”,””,””,””,””,””,””}

We then use the VALUE function to convert numbers in text format to actual numbers. The result is:

{8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

We run this array through ISNUMBER to get:

{TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

Then multiply that times another array with 9 numbers to get:

{1,2,0,0,0,0,0,0,0}

Then we use MAX to get the largest value, which is the position of the “last number”.

Worked Example:   Count numbers that begin with in Excel

In this case we add 1 to the position to get the “unit start” position.

Finally, we use this position with standard LEFT and RIGHT functions to separate the numbers from the units:

=VALUE(LEFT(B5,C5-1)) // number
=TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit

Note that the hard-coded number array constant is a hack for convenience, and will only handle raw values up to 9 characters in length.

Post navigation

Previous Post:

Split text and numbers in Excel

Next Post:

Split dimensions into two parts in Excel Worksheet

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function example in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get last day of month in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next scheduled event in Excel
  • Get days between dates ignoring years in Excel
  • How to calculate working days left in month in Excel

Grouping

  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel

General

  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • With vs Without Array Formula in Excel
  • How to add sequential row numbers to a set of data in Excel
  • How to generate random number between two numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning