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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
how about
=left(A1,find("-",A1))&"()"
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top