2

Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.

How can I separate those characters so as to be able to create a chart of the four individual scores?

  • 2
    In order to create a chart wouldn't those 4 scores need to be in separate cells, rather than simply separated by spaces? – MrWhite Nov 7 '13 at 8:18
3

You could split the cell's value into individual cells. Given that cell A1 contains the number 2113, put the following in cell B1:

=SPLIT(REGEXREPLACE("" & A1, "(\d)", "$1,"), ",")

This should result in cells B1:E1 containing each respective digit.

What the formula does is three things:

  1. The expression "" & A1 converts the input number 2113 to a string.

  2. The REGEXPREPLACE replaces the input string 2113 with a comma-separated list of digits: 2,1,1,3,.

  3. Finally, the SPLIT function splits the comma-separated string into separate cells.

Now that you have each digit in a separate cell, you should be able to create a chart from the data.

If you still want to just add spaces between the digits, try

=REGEXREPLACE("" & A1, "(\d)", "$1 ")
0

The following custom formula will give the result that you want:

Code

// empty string split
function emptySplit(cell) {
  return cell.toString().split('');
}

// same as above, but then joined afterwards, using a space (' ')
function spaceSplit(cell) {
  return cell.toString().split('').join(' ');
}

Explained

The active cell in the spreadsheet is converted to a string (therefore it will work for strings and numbers), and the split method will separate the string using an empty string ('').

Add the little piece of code under Tools>Script editor. Press the save button and you can use your custom formula !!

The SPLIT function in Google Spreadsheets doesn't allow for empty strings to be used as delimiter.

Usage

=emptySplit(A1)

Screenshot

enter image description here

Example

I've created an example file for you: split with empty string as delimiter

0

Another option (more mathematical rather than text) for splitting a four digit number in A1 into four separate cells is in B1 and copied across:

=left(mod($A1,10^(6-column())))

(Assumes no 0 scores!)

0

=REGEXEXTRACT(TO_TEXT(A1); REPT("(.)"; LEN(A1)))

0


=ARRAYFORMULA(REGEXEXTRACT(TO_TEXT(A1:A), REPT("(.)", LEN(A1:A))))

0


=ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(REGEXEXTRACT(
 TO_TEXT(A1:A), REPT("(.)", LEN(A1:A)))), , 999^99)))

0

0

The topmost answer's for REGEXREPLACE no longer works in current version of Sheets. Here's what worked for me:

=REGEXREPLACE(A2;"(\d)"; "$1 ")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.