Retreive part of the value from each cell - Macro and/or forumula

[ucanalways]

Hi Group,

I have the following values in the cells A5 thru A11106.

I am trying to have a macro or a forumula to have the values after =
and without commas in its corresponding cell in the column B.

[1] =,26,
[2] =,126,
[3] =,5626,
[4] =,276,
[5] =,228896,
.....
.....
.....

Result:

26
126
5626
276
228896

The following values in the cells C5 thru C11106. I am trying to write
macro to have the values after = and without commas in its
corresponding cell in the column D.


b[1] =,26,
nuiio[2] =,126gf,
f[3] =,g5626,
dw[4] =,276,
eef[5] =,228896,
.....
......
Result:

26
126gf
g5626
276
228896

I've already in the process of debugging my formula/code to achieve
this but no luck so far. Thought of writing to the group...
Please let me know how can I achieve this.

Thanks,
Kevin

 

[Ron Rosenfeld]

Hi Group,

I have the following values in the cells A5 thru A11106.

I am trying to have a macro or a forumula to have the values after =
and without commas in its corresponding cell in the column B.

[1] =,26,
[2] =,126,
[3] =,5626,
[4] =,276,
[5] =,228896,
....
....
....

Result:

26
126
5626
276
228896

The following values in the cells C5 thru C11106. I am trying to write
macro to have the values after = and without commas in its
corresponding cell in the column D.


b[1] =,26,
nuiio[2] =,126gf,
f[3] =,g5626,
dw[4] =,276,
eef[5] =,228896,
....
.....
Result:

26
126gf
g5626
276
228896

I've already in the process of debugging my formula/code to achieve
this but no luck so far. Thought of writing to the group...
Please let me know how can I achieve this.

Thanks,
Kevin

It can be done with worksheet formulas but that is complex.

One way: download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

You can then use this formula:

=REGEX.MID(A5,"(?<=,)\w+")

(It will work for both of your examples).
--ron

 

[Mauro Gamberini]

A1: ,26
B1: =SUBSTITUTE(A1,",","")

Or:

Public Sub m()

On Error GoTo ErrorHandler

Dim sh As Worksheet
Dim lLastRow As Long

With Application
.ScreenUpdating = False
.Calculation = xlManual
.StatusBar = "Sto eseguendo: Sub m()"
End With

Set sh = Worksheets("Sheet1")

With sh

lLastRow = _
.Range("A" & Rows.Count).End(xlUp).Row
.Range("B1").Value = _
"=SUBSTITUTE(A1,"","","""")"
.Range("B1").Select
Selection.AutoFill _
Destination:=.Range("B1:B" & lLastRow)

End With

ExitRow:
Set sh = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.StatusBar = ""
End With
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbNewLine & Err.Description
Resume ExitRow

End Sub

 

[ucanalways]

A1: ,26
B1: =SUBSTITUTE(A1,",","")

Or:

Public Sub m()

On Error GoTo ErrorHandler

    Dim sh As Worksheet
    Dim lLastRow As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .StatusBar = "Sto eseguendo: Sub m()"
    End With

    Set sh = Worksheets("Sheet1")

    With sh

        lLastRow = _
            .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B1").Value = _
            "=SUBSTITUTE(A1,"","","""")"
        .Range("B1").Select
        Selection.AutoFill _
            Destination:=.Range("B1:B" & lLastRow)

    End With

ExitRow:
    Set sh = Nothing
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .StatusBar = ""
    End With
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume ExitRow

End Sub

--
---------------------------
Mauro Gamberinihttp://www.riolab.org/
<[email protected]> ha scritto nel messaggio

Hi Group,

I have the following values in the cells A5 thru A11106.

I am trying to have a macro or a forumula to have the values after =
and without commas in its corresponding cell in the column B.

[1] =,26,
[2] =,126,
[3] =,5626,
[4] =,276,
[5] =,228896,
....
....
....

Result:

26
126
5626
276
228896

The following values in the cells C5 thru C11106. I am trying to write
macro to have the values after = and without commas in its
corresponding cell in the column D.

b[1] =,26,
nuiio[2] =,126gf,
f[3] =,g5626,
dw[4] =,276,
eef[5] =,228896,
....
.....
Result:

26
126gf
g5626
276
228896

I've already in the process of debugging my formula/code to achieve
this but no luck so far. Thought of writing to the group...
Please let me know how can I achieve this.

Thanks,
Kevin- Hide quoted text -

- Show quoted text -

Thanks guys...