CODEWITHSUNDEEP
excelexcel-formula
SQL-challenged
SQL-challenged

Reputation: 69

Removing hyphens from a cell in Excel

I am looking to take the hyphen or dash out of a birthdate. 01/01/01 01-01-01 I need to combine the birthdate with other fields and it can't have the seperator in it.

I am sure there is a better way. I can get the month and year out, using left & right however I can't figure out how to get the middle out. Below is what I have so far and I know that I can combine them using "&"...if someone can help me with the middle or give me a better equation I would appreciate it.

LEFT(A1,2)

RIGHT(A1,2)

Solution:

=LEFT(F2,2)&MID(F2,4,2)&RIGHT(F2,4)

Upvotes: 0

Views: 9552

Answers (4)

Jon Peltier
Jon Peltier

Reputation: 1

Change the number format of the date to something line "mmddyy". To combine it with other text in another cell, use a formula like:

="The date is "&TEXT(A1,"mmddyy")

where the date is in cell A1.

Upvotes: 0

michaelgilch
michaelgilch

Reputation: 1

You could also do a Find and Replace to replace the "-" with nothing.

Ctrl + H will bring up the Replace dialog box.

Upvotes: 0

Al W
Al W

Reputation: 7713

if it is a string, (not formated as a date) then you can use

MID(A1,4,2)

otherwise i just use the MONTH(A1) DAY(A1) YEAR(A1) functions

Upvotes: 1

Hans Olsson
Hans Olsson

Reputation: 55049

I'm not sure what an excel formula is so this answer might be irrelevant...

VBA has a Mid function that would do what you want I believe. This page has good explanations for that and the other similar functions: http://www.vbtutor.net/VBA/vba9.htm

Upvotes: 0

Related Questions