Formula Help....PLEASE

nsmith103

New Member
Joined
Mar 10, 2009
Messages
3
I'm importing information from two different DB's into excel spreadsheets. The information from both systems should be the same, but differ because of one minor problem. In data base #1 the information that I'm importing into the spreasheets is listed below:

BC-45678
AC-12345
CD-23456
RD-34567


The information from data base #2 that is being imported is listed below:

BC45678
AC12345
CD23456
RD34567

How can I format the column in the first speadsheet (BC-45678) to remove the hyphen w/o having to re-type all 30,000 entries that have been imported into the spreadsheet?

T.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
simply use find and replace.
Select the column and then go to Edit>>Replace
Type "-" (without quotes) in find field and nothing in replace field. Select replace all. That should do it
 
Upvote 0
Hi nsmith103,

Welcome to the forum.

The following formula is one possible way (of many) to do the job - just change the starting cell reference from A2 to suit and filldown as required:

Code:
=LEFT(A2,SEARCH("-",A2)-1)&MID(A2,SEARCH("-",A2)+1,LEN(A2)-SEARCH("-",A2))

HTH

Robert
 
Upvote 0
simply use find and replace.
Select the column and then go to Edit>>Replace
Type "-" (without quotes) in find field and nothing in replace field. Select replace all. That should do it
This did it.... thanks so much.

T.
 
Upvote 0
Hi nsmith103,

Welcome to the forum.

The following formula is one possible way (of many) to do the job - just change the starting cell reference from A2 to suit and filldown as required:

Code:
=LEFT(A2,SEARCH("-",A2)-1)&MID(A2,SEARCH("-",A2)+1,LEN(A2)-SEARCH("-",A2))

HTH

Robert
Thanks Robert, but c m got to me first. I'll jot your info down in my notes.

t.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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