separating hyphen from digits

[honestbud]

Hi guys


I have a column that countains numbers with hyphen.

for example

42 - 54
or
98 - 45 - 12

sometimes I accidentally dont put space befor or after dash.
just like these:
42- 54
or
98 -45 - 12

which are Incorrect for me

is there any way that I can formulate another cell to correct this mistak?

I tried SUBSTITUTE, SEARCH and some other functions along with wildcards, but they didnt help.


thanks

 

[honestbud]

I think I found the solution


=TRIM(SUBSTITUTE(A1,"-"," - "))


Am I right? Any idea?

 

[Peter_SSs]

Am I right? Any idea?

That should work for you.

 

[honestbud]

That should work for you.

thanks. it worked.


but I have another problem now.
I have to put this formula in another cell. And therefor I have to change formulas of many cells.


is there any way that I could conditional format the initial cells, so whenever an incorrect format (hyphen and number without space) appears, I can simply edit the cell?

I came up with solution, but it didnt work:

1- creat another **** name TEST
2- use TRIM and SUBSTITUTE to correct the values
3- use conditional formatting in main sheet to compare value of the cell with that of TEST sheet

conditional formatting formula:

=ADDRESS(ROW(),COLUMN(),4)=ADDRESS(ROW(),COLUMN(),4,,""TEST"")

 

[Peter_SSs]

I came up with solution, but it didnt work:

Even if it did work, it seems this solution would require even more work than the original formula solution that you seem to want to replace.

If you are just trying to eliminate your own manual data entry errors, could you consider using Excel's built-in Data validation to force correct entry format in the first place? That is, force the user to make sure any hyphens are surrounded by single spaces. If so, you could try Data validation like this to see if it is sufficient.

Data -> Data Validation -> Allow: Custom -> Formula:

=SUM(LEN(SUBSTITUTE(A1," - ","")) - LEN(SUBSTITUTE(SUBSTITUTE(A1," - ",""), CHAR(ROW(INDIRECT("48:57"))), ""))) = LEN(SUBSTITUTE(A1," - ",""))

 

[honestbud]

Even if it did work, it seems this solution would require even more work than the original formula solution that you seem to want to replace.

If you are just trying to eliminate your own manual data entry errors, could you consider using Excel's built-in Data validation to force correct entry format in the first place? That is, force the user to make sure any hyphens are surrounded by single spaces. If so, you could try Data validation like this to see if it is sufficient.

Data -> Data Validation -> Allow: Custom -> Formula:

=SUM(LEN(SUBSTITUTE(A1," - ","")) - LEN(SUBSTITUTE(SUBSTITUTE(A1," - ",""), CHAR(ROW(INDIRECT("48:57"))), ""))) = LEN(SUBSTITUTE(A1," - ",""))

thanks. This worked for one cell. I have too many cells in different rows and columns.

what should I do?

 

[Peter_SSs]

thanks. This worked for one cell. I have too many cells in different rows and columns.

what should I do?

1. Copy that cell
2. Select all the other cells where you want it to happen
3. Paste Special ... -> Validation -> OK

Done. If you need more detailed instructions for any of those steps, post back with details.

 

[honestbud]

1. Copy that cell
2. Select all the other cells where you want it to happen
3. Paste Special ... -> Validation -> OK

Done. If you need more detailed instructions for any of those steps, post back with details.

Thanks. you are great. It worked well.



Lets take it to the next level. what if I have already-exsit data table and I want to mark the errors? I think it is not possible to do that with data validation. I think I should use conditional formatting or something like that. I pasted the formula you gave me in conditional formatting, but it didnt work.


what should I do?

thanks in advance

 

[Peter_SSs]

Assuming that you have copied the DV to all the cells where you want it to apply, try these steps

1. Select any one of those cells
2. F5 -> Special... -> Data Validation -> Same -> OK
3. Data ribbon tab -> Data validation -> Circle Invalid Data