0

I need an excel formula that looks in column A and if the cell starts with a number and also has "-" in the cell and then ends with a number column B equals that cell.

Here's an example column A has random text and i want column B to only show the cells that start with a number and end with a number and also have the character "-" in the cell somewhere.

enter image description here

luke
  • 482
  • 4
  • 12
  • 29

2 Answers2

2

I would recommend applying a conditional that requires two things:

1) Numbers before and after a "-" character. That way if there is no "-" the formula throws an error and evaluates false.

2) Since your numbers can be more than a single character long you need to allow for search from the beginning of the cell to the "-" and from the end of the cell to a "-", thus determining the potential length of the number is required.

Try:

=IF(AND(ISNUMBER(VALUE(LEFT(A2,FIND("-",A2)-1))),ISNUMBER(VALUE(RIGHT(A2,LEN(A2)-FIND("-",A2))))),A2,"")

I entered this starting in B2 and filled down. Cheers

fallengyro
  • 150
  • 1
  • 12
0

You can write custom Excel formulas using VB and regex. The regex you need to match that pattern is [0-9][0-9]-[0-9][0-9] (This will match anything from 0-0 up to 99-99).

If you follow the instructions here: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

It will teach you everything you need to know :)

Douglas Stead
  • 150
  • 2
  • 13