Instead of Dash Number Use ()

L

Legacy 436357

Guest
Hello,

I have a very long list of part numbers that I would like to summarize without the dash numbers.

I would like to add "-()" to the end of the first part of the numbers like:

1D937K-1524879 becomes 1D937K-()
52D77-4 becomes 52D77-()
528-58-9999 becomes 528-()

Is there a way to do this please? There is no set number of digits.

Thank you very much for your help,
XJ

PS If there is no dash in the number it needs to stay as is.

Thanks
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,210
Office Version
  1. 365
Platform
  1. Windows
how about
=left(A1,find("-",A1))&"()"
 
L

Legacy 436357

Guest
Thanks but I get a #value ! error when there are no dashes.
 
Last edited by a moderator:

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
If the original number is in cell A1, use this:
=IFERROR(LEFT(A1,FIND("-",A1))&"()",A1)

This finds the hyphen, discards the remainder and adds the brackets to the end. But if there's no hyphen, the FIND function results in an error, which is trapped and the full value of A1 is returned.
 
L

Legacy 436357

Guest
Thank you Trevor that is perfect! Also thank you Fluff for helping too!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can also just make sure that FIND doesn't fail. I did add an IF though in case the cell was blank:
=IF(A1="","",LEFT(A1,FIND("-",A1&"-")-1)&"-()")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,819
Messages
5,525,080
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top