How to remove unwanted data from the column

[Jagdev Singh]

Hi Experts,

We have a column with the data in following manner:

78100270 / BS-TN-21
78058342 / 99-RVS-8
78057468 / BS-JL-05
78053950 / BX-RH-70
78047365 / BX-BV-69
78042732 / BS-SH-63

I want to remove the number which comes after "/" in the column.

The number in the column should be

78100270
78058342
78057468
78053950
78047365
78042732

Regards,
JD

 

[Khalid NGO]

Hi JD,

Go to Find & Replace...
Find what: /*
Leave blank Replace With field and press Replace All.

Regards,

 

[Nebu]

Or you can use the following formula

=TRIM(LEFT(A1,LEN(A1)-SEARCH("/",A1,1)))

Thanks

 

[Jagdev Singh]

Thanks Khalid and Nebu

Both the option worked for me.

Regards,
JD

 

[Jagdev Singh]

Hi Both,

I have one more query. Please let me know how to fix it. I have a sheet with 2 tabs in it. First tab contains date in dd/mm/yyyy and second contain date in mm/dd/yyyy. Do we have formula in excel which will change the format of both the tab in standard either "dd/mm/yyyy" or "mm/dd/yyyy".

Regards,
JD

 

[Khalid NGO]

I think this will work on both formats:
=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Format date as required.

 

[Khalid NGO]

and a short key for dd-mm-yy:
Ctrl+Shift+3

 

[Somendra Misra]

Hi Both,

I have one more query. Please let me know how to fix it. I have a sheet with 2 tabs in it. First tab contains date in dd/mm/yyyy and second contain date in mm/dd/yyyy. Do we have formula in excel which will change the format of both the tab in standard either "dd/mm/yyyy" or "mm/dd/yyyy".

Regards,
JD

Hi JD,

If dates on both the tabs are valid dates, than you need to change the format of one sheet like the second. So no formula required. Formula will be required only if date are in text format or not a valid date?

Regards,

 

[Jagdev Singh]

Hi Somendra

I tried doing that as well, but when I open the mouse on the cell I find different format in the formula bar.

Please find the sample sheet attached.

Regards,
JD

 

[Somendra Misra]

Hi Somendra

I tried doing that as well, but when I open the mouse on the cell I find different format in the formula bar.

Please find the sample sheet attached.

Regards,
JD

On Sheet1 dates are text, convert them to valid dates by Text-to-Column option available under Data Tab, by selecting all the dates. and than you are ready to format in any format on both sheets.

Regards,

 

[Jagdev Singh]

Hi Somendra

Could you please elobrate what exactly I have to do here. I went into Text-to-column option it asked for 2 options, Delimited and Fixed width. If I select the Delimited option then the date gets expand in 3 columns. What to do next. If I follow fixedwith option then what to do with it.

Also, I checked and find few dates '31/12/2010 I want to know how to remove colon " ' " from the begining of such dates.

Regards,
JD

 

[Khalid NGO]

Other than text to columns...
Try this in Sheet1 D2:
=C2+0

copy down.
you will get actual dates.

 

[Jagdev Singh]

Hi Khalid

This worked fine with the Sheet1 and Sheet2. Could you please help with how to remove " ' " sign from front of the dates.

Regards,
JD

 

[Somendra Misra]

Hi Somendra

Could you please elobrate what exactly I have to do here. I went into Text-to-column option it asked for 2 options, Delimited and Fixed width. If I select the Delimited option then the date gets expand in 3 columns. What to do next. If I follow fixedwith option then what to do with it.

Also, I checked and find few dates '31/12/2010 I want to know how to remove colon " ' " from the begining of such dates.

Regards,
JD

Step 1: Select Deliminated. Press Next
Step 2: Select Tab. Press Next
Step 3: Select Date and DMY. Press Finish.

Regards,

 

[Jagdev Singh]

Hi Somendra

Thanks for sharing the above info. I was not aware of it and it even solve the other query of " ' " in the dates. Really appreciate your input.

Regards,
JD