If there is a delimiter present in a text that separates a number, we can use the SPLIT function to split that number from text. But how can we split a number from a text when there is no delimiter present in Google Sheets?

See the below table. I have text strings in the first column without any delimiters, even space characters, to separate numbers from text.

If there is a comma or any other delimiter present between the text and number we can use the SPLIT function to split the numbers from text strings in Google Sheets.

For example, let’s consider the value in cell A1. If it is Sunday,500 we can use the following SPLIT formula.

=split(A1,",")

In order to split numbers from a text when there is no delimiter present, we must insert a delimiter using a formula. In Google Sheets, we can use a REGEXREPLACE formula for that.

That means splitting number from text without delimiter in Google Sheets involves the below two things.

1. First, insert a delimiter(s) into the text using a formula to separate numbers from text.
2. Then use a split formula to split numbers from text.

In concise we can’t directly split a number from a text when there is no delimiter present in Google Sheets. We must insert a delimiter with a Regex formula then split.

How to Insert a Comma Delimiter into a Text to Separate a Number from Text in Google Sheets

We may have different types of texts that contain numbers.

The numbers may be at the beginning of the text, middle of the text or at some other position. Also, there may be multiple separate numbers within a text string. You can see the same in column A in my example above.

Considering all these aspects, we must insert comma delimiters before the beginning of the number and also at the end of it. Then only we can split a number surrounded by texts.

For the text in cell A1, we can use the below REGEXREPLACE formula in cell C1 to insert comma delimiters into the text to separate numbers.

REGEXREPLACE(A1,"([0-9\.]+)",",$1,")

To an entire column range like A1:A, we can make it as an array formula with the help of the IF and ArrayFormula combo.

=ArrayFormula(if(A1:A="",,REGEXREPLACE(A1:A,"([0-9\.]+)",",$1,")))

The ArrayFormula covers column A whereas the IF formula limits the ArrayFormula to the cell contain values.

This way we can insert comma delimiters into text strings to separate numbers from texts in Google Sheets.

Split Formula to Split Numbers from Text without Physical Delimiter

We have inserted delimiters using a formula to separate numbers from text in Google Sheets.

So it’s not wise to say splitting numbers from texts is possible when there is no delimiter present in Google Sheets. Instead, we can say there is no physical delimiter present.

Simply wrapping the above array formula with SPLIT won’t bring the expected result. Then?

The SPLIT function should be inserted just before the REGEXMATCH as below.

=ArrayFormula(if(A1:A="",,split(REGEXREPLACE(A1:A,"([0-9\.]+)",",$1,"),",")))

This formula is in cell C1 and for the result, please refer to the very first screenshot on this page.

I hope, you have found the formula to split numbers from a text without delimiters in Google Sheets useful.

Related Resources

• Split to Column and Categorize – Google Sheets Formula.
• Split a Column into Multiple N Columns in Google Sheets.
• Formula to Reverse Text and Numbers in Google Sheets.
• Split Number to Digits in Google Sheets.
• Extract Numbers Within Square Brackets in Each Row in Google Sheets.
• How to Reverse an Array in Google Sheets [Fixed and Dynamic Array].
• Extract Numbers Prefixed by Currency Signs from a String in Google Sheets.
• Sum Cells With Numbers and Text in a Column in Google Sheets.
• Multiple Column Values into Single Cell in Google Sheets – How-to.
• Extract All Numbers from Text and SUM It in Google Sheets.
• How to Split Text to Columns or Rows in Google Sheets by Delimiter.