VBA - Creating a macro for reducing text in cell

[moreno5]

Guys,

I have been pointed here from another thread as am trying to create a VBA macro that when I press a button the following occurs....


Column B (the date and time) reduced from "20190520 07:38:31.332442 +0000" to just "20190520" which just shows the date.

and

Column U reduced from "20190520 07:38:31.332442 +0000" to just "07:38:31"

and

IF Cell A2 is populated then Cell N2 will be populated with "N" (unless i overwrite)

Of course I will have multiple lines on the spreadsheet so we assume the macro will perform this across all lines.

thanks in advance

[JLGWhiz]

Sub t()
Dim i As Long
    With ActiveSheet
        If .Range("A2").Value <> "" Then .Range("N2").Value = "N"
        For i = 2 To .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
            .Cells(i, 2) = Format(.Cells(i, 2).Value, "yyyymmdd")
            .Cells(i, 2).NumberFormat = "General"
            .Cells(i, 21) = Format(.Cells(i, 21).Value, "Hh:mm:ss")
        Next
    End With
End Sub

[dangelor]

Possibly...

Option Explicit

Sub test()
    Dim v As Variant
    Dim w As Variant
    Dim i As Long
    
    With ActiveSheet
        v = Intersect(.UsedRange, .Columns("B"))
        For i = LBound(v) To UBound(v)
            v(i, 1) = Left(v(i, 1), 8)
        Next i
        Intersect(.UsedRange, .Columns("B")) = v
        
        v = Intersect(.UsedRange, .Columns("U"))
        For i = LBound(v) To UBound(v)
            v(i, 1) = Mid(v(i, 1), 10, 8)
        Next i
        Intersect(.UsedRange, .Columns("U")) = v
        
        v = Intersect(.UsedRange, .Columns("A"))
        w = insersect(.UsedRange, .Columns("N"))
        For i = LBound(v) To UBound(v)
            If Not IsEmpty(v(i, 1)) Then w(i, 1) = "N"
        Next i
        Intersect(.UsedRange, .Columns("N")) = w
    End With
End Sub

[moreno5]

Thanks so far, but that doesn't look to have worked.

I need all date timestamps in column B to be reduced from "20190520 07:38:31.332442 +0000" to just "20190520". So essentially the time removed and just leaving that date format.

then

IF cells in column A are populated then the cell in the same line in column N will be populated with "N" (unless i overwrite).

then

All populated cells in column U reduced from "20190520 07:38:31.332442 +0000" to just "07:38:31" which is just the time HH:MM:SS.


Spreadsheets are now attached for you to look at with what I would like the lines to look like after the macro has run.

[Marc L]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[moreno5]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks Marc, didn't scroll down to see "Manage Attachments". Thanks for your efforts.

[Marc L]

Thanks Marc, didn't scroll down to see "Manage Attachments". Thanks for your efforts.

A starter to paste to the Options booking worksheet module :

Sub Demo1()
    With [A1].CurrentRegion.Rows("2:" & [A1].CurrentRegion.Rows.Count).Columns
        .Item(2).Value = Evaluate("IF({1},LEFT(" & .Item(2).Address & ",8))")
        .Item(14).Value = Evaluate("IF(" & .Item(1).Address & ">"""",""N"","""")")
        .Item(21).Value = Evaluate("IF({1},MID(" & .Item(21).Address & ",10,8))")
    End With
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[jindon]

Column B (the date and time) reduced from "20190520 07:38:31.332442 +0000" to just "20190520" which just shows the date.

Column U reduced from "20190520 07:38:31.332442 +0000" to just "07:38:31"

try

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
End Sub

IF Cell A2 is populated then Cell N2 will be populated with "N" (unless i overwrite)
Of course I will have multiple lines on the spreadsheet so we assume the macro will perform this across all lines.

Don't understand above.

[moreno5]

try

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
End Sub

Don't understand above.

Essentially if a cell is populated in column A...for example A3 has an ID then N3 should be populated with an "N". The same would go for if an ID was now in cell A4 then N4 should be populated with an "N".

[jindon]

Not really sure for the second part.
This will change to "N", if Col.A<>"" and Col.N = "", and no change if col.N has some value already.

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        s = .Address
        With .Columns("n")
            .Value = .Parent.Evaluate("if((" & s & "<>"""")*(" & .Address & "=""""),""N"",if(" & .Address & "<>""""," & .Address & ",""""))")
        End With
    End With
End Sub

[moreno5]

Not really sure for the second part.
This will change to "N", if Col.A<>"" and Col.N = "", and no change if col.N has some value already.

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        s = .Address
        With .Columns("n")
            .Value = .Parent.Evaluate("if((" & s & "<>"""")*(" & .Address & "=""""),""N"",if(" & .Address & "<>""""," & .Address & ",""""))")
        End With
    End With
End Sub

Perfect! That worked superbly. Thank you very much for your help.

[jindon]

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVE

[moreno5]

Actually, sorry to be cheeky....just one last thing as it has worked so nicely.

Any chance we can have it so that again like with the first one where if cell A is populated with anything then cell N in the same line is populated with an "N"....can we also have the following populated into cell V on the same line "ICE.IFLO"

thanks

[jindon]

Try change to

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        s = .Address
        For Each e In Array(Array("n", "N"), Array("v", "ICE.IFLO"))
            With .Columns(e(0))
                .Value = .Parent.Evaluate("if((" & s & "<>"""")*(" & .Address & "=""""),""" & _
                        e(1) & """,if(" & .Address & "<>""""," & .Address & ",""""))")
            End With
        Next
    End With
End Sub

[moreno5]

Hi Jin,

I have just found that I will need need to divide all the prices in column H by 100 with the macro also.

So again, is it possible to have that if cell A on the same line is populated, the figure populated in Cell H of the same line is divided by 100 (So A3 is populated then divide H3 by 100).

thanks,

[moreno5]

Superb and thank you.

[jindon]

Try change to

Sub test()
    Dim e, s
    For Each e In Array(Array("b", Array(" *")), Array("u", Array(".*", "* ")))
        For Each s In e(1)
            Columns(e(0)).Replace s, "", 2
        Next
    Next
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        s = .Address
        For Each e In Array(Array("n", "N"), Array("v", "ICE.IFLO"))
            With .Columns(e(0))
                .Value = .Parent.Evaluate("if((" & s & "<>"""")*(" & .Address & "=""""),""" & _
                        e(1) & """,if(" & .Address & "<>""""," & .Address & ",""""))")
            End With
        Next
        With .Columns("h")
            .Value = .Parent.Evaluate("if(" & s & "<>"""",if(" & .Address & "<>""""," & .Address & "/100,""""))")
        End With
    End With
End Sub

[moreno5]

Thanks again!