Remove a character in a cell

[DQ2013]

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 Available	DATA REQUIRED
GWORX213-BXS	GWORX220.XL
GWORX213-CS	GWORX220.2XL
GWORX213-DM	GWORX220.3XL
GWORX213-EL	GWORX221.XS
GWORX213-FXL	GWORX221.S
GWORX213-G2XL	GWORX221.M
GWORX213-G3XL	GWORX221.L

 

[Zot]

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

 

[Zot]

From your description this is what I understood

Book1
	A	B
1	DATA Available	DATA REQUIRED
2	GWORX213-BXS	GWORX213-XS
3	GWORX213-CS	GWORX213-S
4	GWORX213-DM	GWORX213-M
5	GWORX213-EL	GWORX213-L
6	GWORX213-FXL	GWORX213-XL
7	GWORX213-G2XL	WORX213-2XL
8	GWORX213-G3XL	WORX213-3XL
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SUBSTITUTE(A2,MID(A2,FIND("-",A2)+1,1),"")

 

[Siddharth Rout]

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 Available	DATA REQUIRED
GWORX213-BXS	GWORX220.XL
GWORX213-CS	GWORX220.2XL
GWORX213-DM	GWORX220.3XL
GWORX213-EL	GWORX221.XS
GWORX213-FXL	GWORX221.S
GWORX213-G2XL	GWORX221.M
GWORX213-G3XL	GWORX221.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.

 

[Zot]

Alternative for dot using formula

Book1
	A	B	C
1	DATA Available	DATA REQUIRED	Alternative
2	GWORX213-BXS	GWORX213-XS	GWORX213.XS
3	GWORX213-CS	GWORX213-S	GWORX213.S
4	GWORX213-DM	GWORX213-M	GWORX213.M
5	GWORX213-EL	GWORX213-L	GWORX213.L
6	GWORX213-FXL	GWORX213-XL	GWORX213.XL
7	GWORX213-G2XL	WORX213-2XL	GWORX213.2XL
8	GWORX213-G3XL	WORX213-3XL	GWORX213.3XL
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SUBSTITUTE(A2,MID(A2,FIND("-",A2)+1,1),"")
C2:C8	C2	=SUBSTITUTE(A2,"-"&MID(A2,FIND("-",A2)+1,1),".")

 

[Siddharth Rout]

Alternative for dot using formula

Book1
	A	B	C
1	DATA Available	DATA REQUIRED	Alternative
2	GWORX213-BXS	GWORX213-XS	GWORX213.XS
3	GWORX213-CS	GWORX213-S	GWORX213.S
4	GWORX213-DM	GWORX213-M	GWORX213.M
5	GWORX213-EL	GWORX213-L	GWORX213.L
6	GWORX213-FXL	GWORX213-XL	GWORX213.XL
7	GWORX213-G2XL	WORX213-2XL	GWORX213.2XL
8	GWORX213-G3XL	WORX213-3XL	GWORX213.3XL
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SUBSTITUTE(A2,MID(A2,FIND("-",A2)+1,1),"")
C2:C8	C2	=SUBSTITUTE(A2,"-"&MID(A2,FIND("-",A2)+1,1),".")

I missed the dot part :D

 

[Siddharth Rout]

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