Copying part of a cell to a new cell

[paulw793]

I've got a list from 2008-2012 with 1665 rows of manholes.

What I need to do is go through it and copy the street number from column D to column C and copy the MH# from column D to column E.
Aside from manually going through each on and typing it running the risk of error, is there a VBA code I can put in? Also if there is any way that I could easily delete the street number and MH# from column D afterward it would be awesome...

I've attached the file so you can see what I'm talking about.

[Kelshaer]

for a record that is:
8283 Country Creek #27656 - REPAIR
do you want the value in column E to be:
#27656
or
#27656 - REPAIR

[paulw793]

Just 27656.
No # sign or words after...

I got this on another forum:

enter this in C1027, then copy to your range
=LEFT(D1027;FIND(" ";D1027;1))
and this in E1027, copied to your range:
=IFERROR(RIGHT(D1027;LEN(D1027)-FIND("#";D1027;1));"")
oyu might need to change the ; separator in formulas to your regional settings ,
There are differencies in your data, the formulas or a macro is dificult to adjust to all your types of entries in D column

And then this to only get numbers, but it didn't work:

There is more you can do:
Select the column C, which has formulas, right click on selection then paste back in the same range, but paste only values, so there will be no formulas on column C. Same thing on column E formulas, replace them with the values.
Then , with values in C and E columns,
put this formula in an empty column, same row: 1027:
=SUBSTITUTE(E1027;SUBSTITUTE(C1027;D1027;"");"")
This will remove from column d the data which exists on columns C and E.
Then select this range of substitute formulas, copy, paste only values back on column D.

[Kelshaer]

Here you GO:

Please Login or Register  to view this content.

[JBeaucaire]

There's a lot variation in your data. This formula will get MOST of the MH numbers. Put this formula in E1665 and copy up:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(D1665,FIND("#",D1665)+1,6),"-",""),")",""),"R",""))

[Marcol]

With the variation in your sample I doubt if will be possible to get a 100% solution.

Have a look at this workbook.

I have removed the first section as it appears to have be done.

I have attempted to highlight some of your potential pitfalls, but by no means all of them.

Some can easily be corrected e.g. ...MH 6701 should be MH #6701 for the formulae to work.

"5400 Della Robbia Way- MH #31733-Invert" should be "5400 Della Robbia Way- MH #31733-INVERT"

There are cases where the MH # is in brackets, use Edit > Replace on column D only to remove them, the formula will self correct.

Any others will need to be manually corrected to what ever you need.

Once satisfied you can use Copy > Paste Special > Values to retain the results you need.

Sorry, best I can offer, at least for now.

The forum attachment facility seems to be pregnant again!!! ...

I'll try to explain.
1/. With a copy of your workbook delete the block of rows already completed

2/. In C3

Please Login or Register  to view this content.

Drag/Fill down

3/. In E3

Please Login or Register  to view this content.

Drag/Fill down

4/. Insert a column before Column F in this new Column F3

Please Login or Register  to view this content.

Drag/Fill down
5/. In J3

Please Login or Register  to view this content.

Drag/Fill down

I'll try to post the attachment later.

[Marcol]

Here's the attachment for Post #6

[Kelshaer]

As Marcol Said,
Column "D" data is inconsistent
I modified the code little bit so it will handle most of the cases.

Please Login or Register  to view this content.

[paulw793]

Alright, I've tried all the suggestions in here and Kelshaer's VBA code worked the best.



Thank you all!

[Marcol]

Try this workbook

Please Login or Register  to view this content.

The code could be improved, but it does strip out a bit more.

Yellow Cells have no MH#
Blue Cells begin with numbers
Both need checking against your original data.