Skip to content

Instantly share code, notes, and snippets.

@kamaroly

kamaroly/Extract numbers from Text or String

Created April 30, 2019 10:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kamaroly/235a79e8861c24393c96c8053b5c0163 to your computer and use it in GitHub Desktop.
Save kamaroly/235a79e8861c24393c96c8053b5c0163 to your computer and use it in GitHub Desktop.
Download ZIP
This is an excel formula helps you extract numbers only from text. Replace E2 with cell that has text mixed with number For a text like "We have 17 students" it will return 17
Raw
Extract numbers from Text or String
=SUMPRODUCT(MID(0&E2, LARGE(INDEX(ISNUMBER(--MID(E2, ROW(INDIRECT("1:"&LEN(E2))), 1)) * ROW(INDIRECT("1:"&LEN(E2))), 0), ROW(INDIRECT("1:"&LEN(E2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(E2)))/10),
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment