| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retreive part of the value from each cell - Macor 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retreive part of the value from each cell - Macor and/or forumula
I believe this is the formula you would want:
D5: =SUBSTITUTE(C5,",","") fill down as far as needed. -- ** John C ** " 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retreive part of the value from each cell - Macor and/or forumula
Assuming that
=,26, is a Text string rather than a formula, use =MID(A1,3,LEN(A1)-3) to display 26 -- Gary''s Student - gsnu200810 " 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retreive part of the value from each cell - Macor and/or forumula
Are these entered as Text? Otherwise = sign will convert the entries into
formulas and fail due to ",". If all cells have "=," in the beginning then you can simply have (in B5) =Right(A5,Len(A5)-2) and copy down Adjust the number 2 if there are spaces before or after "=" If you don't want any "," than use Substitute... assuming they are not there due to the format... " 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retreive part of the value from each cell - Macor and/or forumula
In B5, try
=MID(A5,FIND(",",A5)+1,LEN(A5)-FIND(",",A5)-1) Copy down as needed. Copy B5 to D5, then copy down as needed. Hope this helps, Hutch " 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Retreive part of the value from each cell - Macor and/or forumula
Using a formula for column A:
=--SUBSTITUTE(MID(A5,FIND(",",A5)+1,255),",","") Note that if any of the numbers have leading zeros that formula will drop them. [1] =,026, will result in 26. If you have leading zeros then this will include them *but* the result will be a *TEXT* value: =SUBSTITUTE(MID(A5,FIND(",",A5)+1,255),",","") Since column C also contains text characters you need to use the version without the "--". The "--" coerces text numbers to numeric numbers. -- Biff Microsoft Excel MVP wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Macor | New Users to Excel | |||
| Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
| Macor | Excel Discussion (Misc queries) | |||
| How to create a macor to count the usage of another macor | Excel Discussion (Misc queries) | |||
| Is there a forumula that uses colors instead of cell #'s? | Excel Discussion (Misc queries) | |||
Linear Mode
