Computing desk | ||
---|---|---|
< July 25 | << Jun | July | Aug >> | Current desk > |
Welcome to the Wikipedia Computing Reference Desk Archives |
---|
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
I have a large XL spreadsheet that I'm working with. Cells contains digits in ascending order. I wish to reduce instances of double (or more) digits to single digits, e.g. if the cell content is 11111357999, I want to convert it to 13579. My preference is to use the Find and Replace function (control H), as many times as may be necessary. I want to search by Values (because the contents are generated by complex formulae), but I cannot. I can only search by Formulae, and that doesn't help me.
I've found the SUBSTITUTE function, which does what I want. But I want to know why I can't achieve the same result with control H.
Alternatively, is there a function that, in one pass, converts all multiple digits in a particular cell to single digits? -- Jack of Oz [pleasantries] 23:41, 26 July 2019 (UTC)
=IF(ISERROR(FIND("1",A1)),"","1")
. Then we just have to append all the other digits:
=IF(ISERROR(FIND("1",A1)),"","1")
&IF(ISERROR(FIND("2",A1)),"","2")
&IF(ISERROR(FIND("3",A1)),"","3")
&IF(ISERROR(FIND("4",A1)),"","4")
&IF(ISERROR(FIND("5",A1)),"","5")
&IF(ISERROR(FIND("6",A1)),"","6")
&IF(ISERROR(FIND("7",A1)),"","7")
&IF(ISERROR(FIND("8",A1)),"","8")
&IF(ISERROR(FIND("9",A1)),"","9")
Andrew's formula works, but returns the result as a text field. If you'd prefer the result to be a number value, simply add a VALUE statement in there like this:
=VALUE(IF(ISERROR(FIND("1",A1)),"","1")
Note the extra close bracket at the end as well.
Matt Deres (
talk)
11:51, 28 July 2019 (UTC)
&IF(ISERROR(FIND("2",A1)),"","2")
&IF(ISERROR(FIND("3",A1)),"","3")
&IF(ISERROR(FIND("4",A1)),"","4")
&IF(ISERROR(FIND("5",A1)),"","5")
&IF(ISERROR(FIND("6",A1)),"","6")
&IF(ISERROR(FIND("7",A1)),"","7")
&IF(ISERROR(FIND("8",A1)),"","8")
&IF(ISERROR(FIND("9",A1)),"","9"))
Computing desk | ||
---|---|---|
< July 25 | << Jun | July | Aug >> | Current desk > |
Welcome to the Wikipedia Computing Reference Desk Archives |
---|
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
I have a large XL spreadsheet that I'm working with. Cells contains digits in ascending order. I wish to reduce instances of double (or more) digits to single digits, e.g. if the cell content is 11111357999, I want to convert it to 13579. My preference is to use the Find and Replace function (control H), as many times as may be necessary. I want to search by Values (because the contents are generated by complex formulae), but I cannot. I can only search by Formulae, and that doesn't help me.
I've found the SUBSTITUTE function, which does what I want. But I want to know why I can't achieve the same result with control H.
Alternatively, is there a function that, in one pass, converts all multiple digits in a particular cell to single digits? -- Jack of Oz [pleasantries] 23:41, 26 July 2019 (UTC)
=IF(ISERROR(FIND("1",A1)),"","1")
. Then we just have to append all the other digits:
=IF(ISERROR(FIND("1",A1)),"","1")
&IF(ISERROR(FIND("2",A1)),"","2")
&IF(ISERROR(FIND("3",A1)),"","3")
&IF(ISERROR(FIND("4",A1)),"","4")
&IF(ISERROR(FIND("5",A1)),"","5")
&IF(ISERROR(FIND("6",A1)),"","6")
&IF(ISERROR(FIND("7",A1)),"","7")
&IF(ISERROR(FIND("8",A1)),"","8")
&IF(ISERROR(FIND("9",A1)),"","9")
Andrew's formula works, but returns the result as a text field. If you'd prefer the result to be a number value, simply add a VALUE statement in there like this:
=VALUE(IF(ISERROR(FIND("1",A1)),"","1")
Note the extra close bracket at the end as well.
Matt Deres (
talk)
11:51, 28 July 2019 (UTC)
&IF(ISERROR(FIND("2",A1)),"","2")
&IF(ISERROR(FIND("3",A1)),"","3")
&IF(ISERROR(FIND("4",A1)),"","4")
&IF(ISERROR(FIND("5",A1)),"","5")
&IF(ISERROR(FIND("6",A1)),"","6")
&IF(ISERROR(FIND("7",A1)),"","7")
&IF(ISERROR(FIND("8",A1)),"","8")
&IF(ISERROR(FIND("9",A1)),"","9"))