Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to extract numbers only from text strings in Google Sheet
Google sheets is a web based application that acts like a Software as a service in offering similar fundamental functionality to excel. With this functionality, we can accomplish many common spreadsheet tasks, and in this tutorial we will look into various ways of extracting just the numbers from text strings in google sheets.
Using the REGEXEXTRACT Function
Here we look at the simple case where the numbers are all sequential in one substring of the text given.
Step 1 − Open google sheets and input all your texts into the respective cells.
Step 2 − Use the REGEXEXTRACT function with the first parameter being the first cell in question, and the second parameter being “\d+”. This represents one or more (represented by the +) digits (represented by the d).
Step 3 − Now to autofill grab the answer to the individual cell answer done by the bottom right corner and scroll through all the cells you want filled
Using the REGEXREPLACE Function
Sometimes we encounter texts where we have alternating sequences of letters and numbers where numbers would appear continuously in more than one subsequence of the text. Here it would be better to use the REGEXREPLACE function where we can just replace the non-digits with an empty string (nothing) to essentially remove them.
Step 1 − Open google sheets and input all your texts into the respective cells.
Step 2 − Use the REGEXREPLACE function with the first parameter being the first cell in question, the second parameter being “[^\d+]”, and the last parameter being “”. The second parameter regular expression here takes the negation (^) of all digits (non-digits), and this is being replaced with the third, which is empty so we are essentially removing what is being described in the regular expression in the second parameter.
Step 3 − Now to autofill grab the answer to the individual cell answer done by the bottom right corner and scroll through all the cells you want filled.
9K+ Views
