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

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

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

On Tue, 28 Oct 2008 10:13:11 -0700 (PDT), wrote:

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

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

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 Gamberini
http://www.riolab.org/
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

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

On Oct 28, 10:46*am, "Mauro Gamberini"
wrote:
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/
ha scritto nel ...



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...