MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Filling in columns counting by Hex

• Follow

```3 columns:
A                       B                      C
042-0-0-01234   100.100.1.210   00-00-00-00-04-D2

I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, etc
Column C will have a MAC and the last 2 segments are based on the last 5 of
column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
Column B is decimal of last segment of column C, e.g. D2 = 210

I would like to scroll down column A, and fill in column B anc C, which I am
doing manual right now.

Way beyond me.....Any ideas?

Thanks,
Ron
```
 0
Reply Utf 3/31/2010 7:30:01 PM

```Oh yeah...if it could go down a certain number of rows, which I would tell
it, or until it hit a filled cell in Column B or C...that would help.

Thanks, wizards

"Ron" wrote:

> 3 columns:
> A                       B                      C
> 042-0-0-01234   100.100.1.210   00-00-00-00-04-D2
>
> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, etc
> Column C will have a MAC and the last 2 segments are based on the last 5 of
> column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
> Column B is decimal of last segment of column C, e.g. D2 = 210
>
> I would like to scroll down column A, and fill in column B anc C, which I am
> doing manual right now.
>
> Way beyond me.....Any ideas?
>
> Thanks,
> Ron
```
 0
Reply Utf 3/31/2010 7:33:02 PM

```Hi Ron,

Assuming that the first value is in A2 insert the following formulas.

In C2 enter
="00-00-00-00-"&LEFT(DEC2HEX(RIGHT(A2,5),4),2)&"-"&RIGHT(DEC2HEX(RIGHT(A2,5),4),2)

In B2 enter  ="100.100.1."&HEX2DEC(RIGHT(C2,2))

Copy the formulas down the columns.

--
Regards,

OssieMac

"Ron" wrote:

> Oh yeah...if it could go down a certain number of rows, which I would tell
> it, or until it hit a filled cell in Column B or C...that would help.
>
> Thanks, wizards
>
> "Ron" wrote:
>
> > 3 columns:
> > A                       B                      C
> > 042-0-0-01234   100.100.1.210   00-00-00-00-04-D2
> >
> > I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, etc
> > Column C will have a MAC and the last 2 segments are based on the last 5 of
> > column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
> > Column B is decimal of last segment of column C, e.g. D2 = 210
> >
> > I would like to scroll down column A, and fill in column B anc C, which I am
> > doing manual right now.
> >
> > Way beyond me.....Any ideas?
> >
> > Thanks,
> > Ron
```
 0
Reply Utf 3/31/2010 7:51:02 PM

```You didn't tell us how the front part of the values in Columns B and C are
specified, so I hard-coded what you showed us into the following macro (you
will have to adjust this on your own or tell us how this information is
derived so we can show you how to do it). Also your specification on which
cells to operate on was kind of sketchy, so I took the easy way out and
simply made the code operate on each cell in the current selection...

Sub HexSplitter()
Dim Cell As Range, HexNum As String
For Each Cell In Selection
HexNum = Right("0000" & Hex(Right(Cell.Value, 5)), 4)
Cell.Offset(0, 1).Value = "100.100.1." & CLng("&H" & Right(HexNum, 2))
Cell.Offset(0, 2).Value = "00-00-00-00-" & Format(HexNum, "@@-@@")
Next
End Sub

--
Rick (MVP - Excel)

"Ron" <Ron@discussions.microsoft.com> wrote in message
news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@microsoft.com...
> Oh yeah...if it could go down a certain number of rows, which I would tell
> it, or until it hit a filled cell in Column B or C...that would help.
>
> Thanks, wizards
>
> "Ron" wrote:
>
>> 3 columns:
>> A                       B                      C
>> 042-0-0-01234   100.100.1.210   00-00-00-00-04-D2
>>
>> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235,
>> etc
>> Column C will have a MAC and the last 2 segments are based on the last 5
>> of
>> column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
>> Column B is decimal of last segment of column C, e.g. D2 = 210
>>
>> I would like to scroll down column A, and fill in column B anc C, which I
>> am
>> doing manual right now.
>>
>> Way beyond me.....Any ideas?
>>
>> Thanks,
>> Ron

```
 0
Reply Rick 3/31/2010 7:58:16 PM

```Sorry guys, I've been away...thanks for the responses, I'll see what works
and let you know.

"Rick Rothstein" wrote:

> You didn't tell us how the front part of the values in Columns B and C are
> specified, so I hard-coded what you showed us into the following macro (you
> will have to adjust this on your own or tell us how this information is
> derived so we can show you how to do it). Also your specification on which
> cells to operate on was kind of sketchy, so I took the easy way out and
> simply made the code operate on each cell in the current selection...
>
> Sub HexSplitter()
>   Dim Cell As Range, HexNum As String
>   For Each Cell In Selection
>     HexNum = Right("0000" & Hex(Right(Cell.Value, 5)), 4)
>     Cell.Offset(0, 1).Value = "100.100.1." & CLng("&H" & Right(HexNum, 2))
>     Cell.Offset(0, 2).Value = "00-00-00-00-" & Format(HexNum, "@@-@@")
>   Next
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
>
> "Ron" <Ron@discussions.microsoft.com> wrote in message
> news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@microsoft.com...
> > Oh yeah...if it could go down a certain number of rows, which I would tell
> > it, or until it hit a filled cell in Column B or C...that would help.
> >
> > Thanks, wizards
> >
> > "Ron" wrote:
> >
> >> 3 columns:
> >> A                       B                      C
> >> 042-0-0-01234   100.100.1.210   00-00-00-00-04-D2
> >>
> >> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235,
> >> etc
> >> Column C will have a MAC and the last 2 segments are based on the last 5
> >> of
> >> column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
> >> Column B is decimal of last segment of column C, e.g. D2 = 210
> >>
> >> I would like to scroll down column A, and fill in column B anc C, which I
> >> am
> >> doing manual right now.
> >>
> >> Way beyond me.....Any ideas?
> >>
> >> Thanks,
> >> Ron
>
> .
>
```
 0
Reply Utf 4/7/2010 8:06:10 PM

```Well, my screaming to the engineers solved this. They are providng me with a
copy of their spreadsheet, with this all filled in. Both of these seem to
work for me and I use them differently on whether I needed a lot of a few.

Thanks so much for the help.

Ron

"Ron" wrote:

> Sorry guys, I've been away...thanks for the responses, I'll see what works
> and let you know.
>
> "Rick Rothstein" wrote:
>
> > You didn't tell us how the front part of the values in Columns B and C are
> > specified, so I hard-coded what you showed us into the following macro (you
> > will have to adjust this on your own or tell us how this information is
> > derived so we can show you how to do it). Also your specification on which
> > cells to operate on was kind of sketchy, so I took the easy way out and
> > simply made the code operate on each cell in the current selection...
> >
> > Sub HexSplitter()
> >   Dim Cell As Range, HexNum As String
> >   For Each Cell In Selection
> >     HexNum = Right("0000" & Hex(Right(Cell.Value, 5)), 4)
> >     Cell.Offset(0, 1).Value = "100.100.1." & CLng("&H" & Right(HexNum, 2))
> >     Cell.Offset(0, 2).Value = "00-00-00-00-" & Format(HexNum, "@@-@@")
> >   Next
> > End Sub
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "Ron" <Ron@discussions.microsoft.com> wrote in message
> > news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@microsoft.com...
> > > Oh yeah...if it could go down a certain number of rows, which I would tell
> > > it, or until it hit a filled cell in Column B or C...that would help.
> > >
> > > Thanks, wizards
> > >
> > > "Ron" wrote:
> > >
> > >> 3 columns:
> > >> A                       B                      C
> > >> 042-0-0-01234   100.100.1.210   00-00-00-00-04-D2
> > >>
> > >> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235,
> > >> etc
> > >> Column C will have a MAC and the last 2 segments are based on the last 5
> > >> of
> > >> column A. e.g. 01234 = 04D2 (first 4 segments are fixed)
> > >> Column B is decimal of last segment of column C, e.g. D2 = 210
> > >>
> > >> I would like to scroll down column A, and fill in column B anc C, which I
> > >> am
> > >> doing manual right now.
> > >>
> > >> Way beyond me.....Any ideas?
> > >>
> > >> Thanks,
> > >> Ron
> >
> > .
> >
```
 0
Reply Utf 4/8/2010 5:12:09 PM

5 Replies
1035 Views

(page loaded in 0.14 seconds)

Similiar Articles:

7/23/2012 5:29:53 AM

 Reply: