separating hyphen from digits

honestbud

New Member
Joined
Jul 8, 2018
Messages
5
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

honestbud

New Member
Joined
Jul 8, 2018
Messages
5
I think I found the solution


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


Am I right? Any idea?
 

honestbud

New Member
Joined
Jul 8, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 8, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 8, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,397
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,258
Messages
5,674,669
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top