1. H

    Use > or < for hyphenated cell

    hi Is it possible to check greater than or less than in a cell with a hyphenated number? I do not wish to create a further helper column unless that is the only way to achieve what I am after. I have a sheet with football scores in it and they come like this 1-0, 2-2, 2-1 etc. Is it possible...
  2. D

    Conditional Formatting Letter Grades - "Minuses" Not Working

    Hello, I'm sure it's out there somewhere but I haven't been able to find it yet. So sorry, everyone, I'm pulling you into this. I have created a worksheet of letter grades and would like to do a conditional format color fill of the cells for every letter grade in the same cell. These grades...
  3. J

    Data validation and numbers + hyphens

    Hi there all, i want to set a data validation rule so that only the following number sequence can be entered to a cell: 000000-00 the hyphen is a must, if i didnt need the hyphen I would be fine setting this up. Any ideas? I use the data validation from the ribbon, and need a custom formula
  4. E

    Remove hyphen and separate / copy numbers

    Hi all.. So my problem is as follows and not sure if it can be done... In Column B and starting in B2. I have descending data in format of 1-2-3 I need a macro to remove the hyphen, then copy 1 to C2, 2 to D2 and 3 to E2 This needs to be repeated down entire B column and on different sheets...
  5. P

    Format Cells

    Hello. I have cells that contain dates in the following formats: "9/13-" "9/13-9/14" "-9/14" What I am trying to determine is if I can force the "hyphen" into the cell. So if I type: "9/13" The format will auto-update to: "9/13-" I formulas that are based on that "-" being there. How can...
  6. B

    rearrange text in cell (more complex than a first name surname switch!)

    Hello, I would love your help with this. I have a column of cells with text which always takes the following form: aaaaaaaaa - bbbbbbbb, cccccccccc, dddddddddd, eeeeeeeeeee, ffffffffffffffff, etc Is there a formula I could use in another column to reorder this data into the following...
  7. M

    Adding Hyphen in Between of Strings Using Macros

    Hello Mates, i want to insert a hyphen in between of strings in the A column. My string format is AA111AA111 BB222BB222,....... A space is separating the strings. Number of strings are dynamic. Hyphen should be inserted exactly middle of the string that is after 5th character. So the result...
  8. M

    Using VBA to return data before the last hyphen

    Hello all Thanks again for this great forum. It is such a great learning curve. I am wondering if somebody could help me with a query. The BI dept at work send me on a weekly basis a report of SKU IDs and total qtys, but with the SKU description added to the SKU ID by a hypen. Some SKU IDs...
  9. C

    add a hyphen on a 9 digit zipcode

    Hi guys, I am new to using excel. so here's what I'm trying to do: Some US zip codes have 9 digits and I want to a Hyphen "-" after the first 5 digits in a column. However, the same column also includes 5 digit ones so I am trying to use =IF(LEN(a2)>5,(REPLACE(a2,6,0,"-"))) but I can't seem to...
  10. H

    Remove excess punctuation

    I have 1000s of lines of numbers in the following format: 0000-00-000-0000 and I need to remove the last hyphen so the data looks like: 0000-00-0000000. Can anyone help?
  11. D

    ISBLANK and IF inside a VLOOKUP

    I have a table of information. Let's call this Table 1. Table 1 <tbody> A B C 1 Activity TNumber ID 2 Training 54465 3 Exercise A ABC111A 91299 4 Exercise B ABC222B 91300 </tbody> This second table (let's call this Table 2) allows the user to select an Activity from a dropdown...
  12. C

    Joining Text with Hypen

    I have asked this before in this forum (https://www.mrexcel.com/forum/excel-questions/1062061-separating-characters-hyphen.html#post5100161), but have ran into a hiccup. Cell AJ2 has "Khaki" without quotes Cell AJ3 has "Medium Gray" without quotes In cell K2 I have (=SUBSTITUTE(AJ2&"/"&AK2...
  13. S

    How to exclude anything that follows the last hyphen that starts with Text (A-Z)

    Hey all! I'm new here. I'm having some trouble with my formula. Im trying to get rid of everything after the last hyphen in the respective cells. here is what i have so far. =IF(LEN([@[column1]])<=10|[@[column2]]|IF(RIGHT([@[column1]]|1)="-"|LEFT([@[column1]]|LEN(SUBSTITUTE([@[Andrew''s...
  14. R

    IP Formatting Issues (asking for VBA help!)

    Hi All, I have messed about with this for hours with zero progress so I'm going to open this up to the room in hopes somebody can be a lifesaver. I've taken on a project of updating IP ranges. I have close to about 2 million of these to work through and our system has generated the IP's in a...
  15. H

    separating hyphen from digits

    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...
  16. D

    VBA to split out hyphen strings into comma delimited

    Hi, Is it possible to split a hyphen string into comma delimited. Example: From This <tbody> A1-10 J1D1_4-J1D1_7 C2, C4-C8, C12 </tbody> To This <tbody> A1, A2, A3, A4, A, A, A7, A8, A9, A10 J1D1_4, J1D1_5, J1D1_6, J1D1_7 C2, C4, C5, C6, C7, C8, C12 </tbody>
  17. S

    Macro to to clear leading apostrophes, but not shading?

    I have a Worksheet with 7,000 rows, some of which have shading. In one of the columns, I started the last 4-digits of a credit card number with a hyphen. Over time, I have come to see that if you start the numbers with a hyphen, it requires you to have the hidden, leading apostrophe, which is...
  18. B

    Concatenate 2 cells and separate with 2 hyphens

    Ok my friends, i have searched for concatenating 2 cells with hyphens and have found several solutions. I cannot however see what it is that i am after. I would like to concatenate 2 values e.g 2453 in cell 1 and 987654321 in cell 2. I have seen how to do the first hyphen using...
  19. F

    Sum values seperated by a hyphen

    How can I sum these values that are separated by a hyphen other than using =LEFT(A2,FIND("-",A2)-1)+MID(A2,FIND("-",A2)+1,2) There will always only be one hyphen and the two values will never be over two digits on each side of the hyphen. Data Range <tbody> A B 1 Count Total 2...
  20. S

    Sorting numbers before hyphen and within range

    Hi Folks, Sorry if I sound silly. But, is there a way to sort by the last two digits before the hyphen, without removing the numbers after the hyphen? ie. 12306-97 , 123000-17, 154312-15, 965434-18, etc. I can do this slowly by combing through the spreadsheet but it is inefficient and I end up...

Watch MrExcel Video

This Week's Hot Topics

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