hyphen

  1. 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
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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?
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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>
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. sharky12345

    Check if cell contains part of a string

    I have a cell which has a formula result in it which at the moment is like this; That cell can have any one of a number of different values in different formats, but I'm trying to find a formula that can tell me if the cell value has a hyphen in it. I thought doing something like *-* would...
  20. K

    Reformatting data on MANY sheets

    My Excel document has approximately 1100 worksheets. Each has a varying number (some have 2 or 3, some have 110) of rows of data. Each row looks something like this (I have used commas to express columns) from column A to D: 1931 , 04-15 , Name , SC I would like to to have all of these rows...

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top