Copying part of a cell to a new cell

paulw793 · #1 01-27-2012, 06:13 AM

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.

Catalin.B · #2 01-27-2012, 07:02 AM

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

paulw793 · #3 01-27-2012, 07:13 AM

Quote:

Originally Posted by Catalin.B

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

Holy crap! That's amazing!
Thank you!

Is there anyway to add onto the second formula to only copy digits and not characters?

Catalin.B · #4 01-27-2012, 07:27 AM

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.

paulw793 · #5 01-27-2012, 08:03 AM

Quote:

Originally Posted by Catalin.B

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.

That formula isn't working correctly. When I enter it in all it does is give me the same value that is in column E and doesn't delete anything from column D.

Catalin.B · #6 01-27-2012, 08:28 AM

this is the right one, the other was written without testing, sorry.
=SUBSTITUTE(SUBSTITUTE(D1027;C1027;"");E1027;"")

Catalin.B · #7 01-27-2012, 09:41 AM

Quote:

Originally Posted by paulw793

Is there anyway to add onto the second formula to only copy digits and not characters?

To extract numbers from text, best is to use a macro, like this:
(can be set to extract decimal, and/or negative numbres, with the optional arguments:

Code:

Function ExtrageNumar(rCell As Range, _
     Optional CuZecimale As Boolean, Optional Negative As Boolean) As Double


Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
    
    sText = rCell
    If CuZecimale = True And Negative = True Then
        strNeg = "-"
        strDec = ","
    ElseIf CuZecimale = True And Negative = False Then
        strNeg = vbNullString
        strDec = ","
    ElseIf CuZecimale = False And Negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If

    iLoop = Len(sText)
            For iCount = iLoop To 1 Step -1
                vVal = Mid(sText, iCount, 1)
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount

    ExtrageNumar = CDbl(lNum)

End Function

Then, in cell, use : =ExtrageNumar(E8;1;1) (second and third arguments are optional, and are used for negative and decimals)
note:
the code may need to be modified according to your regional settings:
on lines 13 and 16, strDec = "," is strDec = "." if this is your regional decimal delimiter

paulw793 · #8 01-27-2012, 10:46 AM

Thanks Catilan! I got it all figured out!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I delete the content of a cell in column if the cell value is more than 1000?	Learner7	Excel	2	06-27-2011 05:44 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM
How can I make a cell color RED under the Today's date cell?	Learner7	Excel	2	07-08-2010 12:52 AM
Copying data from one cell to another automatically	mrphilk	Excel	4	06-10-2010 11:52 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM