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

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • How to calculate current stock or inventory in Excel
  • How to Use Solver Tool in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How to count table rows in Excel

References

  • How to retrieve first match between two ranges in Excel
  • How to get address of first cell in range in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • Merge tables with VLOOKUP in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • Excel Data validation with conditional list
  • How To Create Drop-down List in Excel

Convert feet and inches to inches in Excel

by

To convert a measurement in feet and inches to inches only (i.e. 4’5″ to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. See illustration below:

Formula

=LEFT(B5,FIND("'",B5)-1)*12+SUBSTITUTE
(MID(B5,FIND("'",B5)+1,LEN(B5)),"""","")

Explanation

In the example shown, the formula in D5 is:

=LEFT(B5,FIND("'",B5)-1)*12+SUBSTITUTE
(MID(B5,FIND("'",B5)+1,LEN(B5)),"""","")

How this formula works

In the first part of the formula, feet are extracted and converted LEFT and FIND like this:

=LEFT(B5,FIND("'",B5)-1)*12

Working from the inside out, FIND is used to locate the position of the single quote (‘) in the string. This number (minus one) goes into the LEFT function as the number of characters to extract. For cell B5, LEFT returns 8, which is then multiplied by 12 to get inches.

Worked Example:   Highlight cells that contain in Excel

Note: LEFT will return text, but multiplying by 12 will covert the text a number.

In the second part of the formula, we extract the inches part of the text value with this:

SUBSTITUTE(MID(B5,FIND("'",B5)+1,LEN(B5)),"""","")

Here we again locate the position of the single quote (‘) in the string with FIND. This time however we add one, and feed the result into the MID function as the starting point for extracting characters.

Worked Example:   Convert inches to feet and inches in Excel

For the number of characters to extract, we cheat and use the LEN function. LEN will return the total characters in B5, which is a larger number than exist after the single quote in the string. however, MID will simply extract all remaining characters. For B5, MID will return ” 4″””, which goes into the SUBSTITUTE function as text.

Worked Example:   How to Separate Text Strings in Excel

SUBSTITUTE is configured to look for a double quote (“”””) and replace with nothing (“”). In the case of B5, SUBSTITUTE returns ” 4″ as text, which is added to the feet number already determined. As before, the math operation converts the text a number, and Excel returns a final result of 100.

Other units

Once you have the measurement in inches, you can use the CONVERT function to convert to other units of measure.

Post navigation

Previous Post:

How to generate random date between two dates in Excel

Next Post:

Popularly Used Excel Functions and their examples

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

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • Complete List of Excel Logical Functions, References and Examples
  • Check multiple cells are equal in Excel

Date Time

  • Get days before a date in Excel
  • List holidays between two dates in Excel
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate working days left in month in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel
  • Map text to numbers in Excel

General

  • Index and match on multiple columns in Excel
  • Count cells less than in Excel
  • Subtotal by invoice number in Excel
  • Excel Operators
  • How to generate random date between two dates in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning