I wanted to search for a space in some text and used SEARCH, but if no space is found I get an error. So I used something like this
“=IF(ISNUMBER(SEARCH(” “,F2,1)),TRUE,FALSE)”. If it is TRUE I can put the SEARCH in that part of the formula and I will get the position of the space and if it is false I can simply put in a zero.
“=IF(ISNUMBER(SEARCH(” “,F2,1)),SEARCH(” “,F2,1),0)”
This seems real klutzy. Is there another command I can use or a better way to do this?
Why? I am attempting to clean up someone’s database with names in one field. The person uses spaces to move the text to the right, away from the left border of the cell, instead of an indent and uses a comma and two spaces (generally) between the last and first name and then some spaces (generally one) between the first name and the maiden name and then somewhere down the line puts in a spouse in parentheses.
SpaceJohnson,SpaceSpaceJennifer (Robert)
Johnson, Jennifer Wilson (Robert)
He also used a fancy script font so one cannot tell where there are spaces or how many. Not nearly as clean as the line above.
—–
Dennis
If your intention is to clean up those cells, first I would simply select them all and change the font. So that solves that problem. Then I would use the new REGEX functions to create versions of the text that eliminate any place where there is more than one space, and any place where the text starts with spaces.
For instance, if the text is in column B, then in column C, the formula SUBSTITUTE(B2,REGEX(" +")," ") will replace any place there is more than 1 space with just one space. Then you can copy all of those cells, and use Edit, Paste Formula Results to make those changes permanent.
Then this one will remove any spaces at the start:
SUBSTITUTE(B2,REGEX("^ +"),"")
Removing the spouse names can be done as well, but how many are there? If we are talking about a few dozen, then maybe just do that by hand. If we are talking hundreds, then you can research a REGEX formula to extract that and also to remove everything from the cell.
For instance, this will get the text in the parenthesis:
REGEX.EXTRACT(B2,"\((.*)\)",1,1)
Then this will give you the text of everything, removing what is in parenthesis
SUBSTITUTE(B5,REGEX("\(.*\)"),"")