Wikipedia:Reference desk/Archives/Computing/2019 July 26

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.

July 26

XL Replace function query

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)

That's a job for a macro, not a function. Time to learn a bit of VBA. Andy Dingley (talk) 11:02, 27 July 2019 (UTC)

Alternatively, you may be able to calculate the unfiltered values, export them as a CSV file, then import them back in. The formulas will be removed at that point, leaving only values. You can run your SUBSTITUTE functions then, or on the CSV file as a REPLACE function (CTRL R). Be sure to save under a different name, so you don't overwrite the version with the formulas. SinisterLefty (talk) 12:40, 27 July 2019 (UTC)

If you're searching for any more information about the spreadsheet program you're using, it's called Excel, not "XL".--Thomprod (talk) 13:02, 27 July 2019 (UTC)

Excellent comment. SinisterLefty (talk) 13:06, 27 July 2019 (UTC)

I've been eating too much jam. -- Jack of Oz ^{[pleasantries]} 20:58, 1 August 2019 (UTC)

I had a complicated formula (like the one I gave for your question in May), but as I was editing my answer I realised there's a much (well, slightly) easier solution. The algorithm goes: if there's a 1 in the value, write out a single 1, otherwise nothing; then similarly for 2,3,...9. (I'm assuming zero is not a possible digit). The formula for the 1 part (with the original in A1) is =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")


which you can enter with the line breaks for readability. However, if you're going to be doing much more of this kind of thing, I'd echo Andy's "Time to learn a bit of VBA" comment. AndrewWTaylor (talk) 14:02, 27 July 2019 (UTC)

Just to explain what's going on here, the FIND(<needle>,<haystack>) function tells you at what position in <haystack> the string <needle> occurs. Rather messily, if the haystack doesn't contain the needle then the function returns the error #VALUE! (rather than 0, as one might expect) so we have to use ISERROR() to detect this. AndrewWTaylor (talk) 14:08, 27 July 2019 (UTC)

Andrew, once again many thanks for your help. -- Jack of Oz ^{[pleasantries]} 22:27, 27 July 2019 (UTC)

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")
&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"))
Note the extra close bracket at the end as well. Matt Deres (talk) 11:51, 28 July 2019 (UTC)

And thank you, Matt. -- Jack of Oz ^{[pleasantries]} 01:27, 29 July 2019 (UTC)