Remove a character in a cell

DQ2013

New Member
Joined
Nov 7, 2013
Messages
31
I am hoping someone could help me with the following:
I am after a formula that will remove the first character after the '-' and will look like the "Data required" column

DATA AvailableDATA REQUIRED
GWORX213-BXSGWORX220.XL
GWORX213-CSGWORX220.2XL
GWORX213-DMGWORX220.3XL
GWORX213-ELGWORX221.XS
GWORX213-FXLGWORX221.S
GWORX213-G2XLGWORX221.M
GWORX213-G3XLGWORX221.L
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I can't see the relation between for example
From GWORX213-BXS to GWORX220.XL after removing the first character after -. Seems like the 20.XL came out from no where :unsure:
 
Upvote 0
I am hoping someone could help me with the following:
I am after a formula that will remove the first character after the '-' and will look like the "Data required" column

DATA AvailableDATA REQUIRED
GWORX213-BXSGWORX220.XL
GWORX213-CSGWORX220.2XL
GWORX213-DMGWORX220.3XL
GWORX213-ELGWORX221.XS
GWORX213-FXLGWORX221.S
GWORX213-G2XLGWORX221.M
GWORX213-G3XLGWORX221.L

You do not need a formula for this. What I am going to suggest is specific to your request. Use Find and Replace with Wildcard.

  1. Highlight the column which has the data.
  2. Press Ctr + H
  3. In the Find What section type "-?" without the quotes
  4. In the Replace With section type "-" without the quotes
  5. Click on Replace All.

1660800813503.png
 

Attachments

  • 1660800499628.png
    1660800499628.png
    45.2 KB · Views: 16
Upvote 0
Upvote 0
Alternative for dot using formula
Book1
ABC
1DATA AvailableDATA REQUIREDAlternative
2GWORX213-BXSGWORX213-XSGWORX213.XS
3GWORX213-CSGWORX213-SGWORX213.S
4GWORX213-DMGWORX213-MGWORX213.M
5GWORX213-ELGWORX213-LGWORX213.L
6GWORX213-FXLGWORX213-XLGWORX213.XL
7GWORX213-G2XLWORX213-2XLGWORX213.2XL
8GWORX213-G3XLWORX213-3XLGWORX213.3XL
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=SUBSTITUTE(A2,MID(A2,FIND("-",A2)+1,1),"")
C2:C8C2=SUBSTITUTE(A2,"-"&MID(A2,FIND("-",A2)+1,1),".")

I missed the dot part :D
 
Upvote 0
I am hoping someone could help me with the following:


You do not need a formula for this. What I am going to suggest is specific to your request. Use Find and Replace with Wildcard.

  1. Highlight the column which has the data.
  2. Press Ctr + H
  3. In the Find What section type "-?" without the quotes
  4. In the Replace With section type "-" without the quotes
  5. Click on Replace All.

View attachment 71883

A small change.

In the Replace With section type "." without the quotes
 
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