Extract some data from a string

**Str1der** · 05-19-2016, 10:42 AM

Hello, so following on from:

http://www.excelforum.com/excel-gene...some-data.html

I have some MAC addresses listed as the result of a formula:

Please Login or Register  to view this content.

Displayed value = b499.ba7a.72a1

On another sheet I have many rows of data, this is an example of a row:

10.188.104.180 0017a4773c50 stratsoln-uat-vr/eth2/1.466 VLD 382 0 0 199

There is a 1-1 mapping of a MAC address to an IP address.

Some have spaces in front of the IP, some don't

As you can see, the formats of the MAC address are different, but last 4 digits of the MAC should be unique.

I would like to automate searching for 72a1 and discovering which IP it is using, without having to manually Ctrl+F. Ideally it would also place that IP value in the column one to the right of the MAC.

Hope that makes sense

**Richard Buttrey** · 05-19-2016, 10:48 AM

Hi,

Please upload the workbook with a representative sample of data and manually add the results you expect to see clearly explaining which are the result cells. It's always easier if we can see in context.

**Str1der** · 05-19-2016, 11:08 AM

Upload attached

**Str1der** · 05-20-2016, 05:46 AM

Any ideas?

**Kevin#** · 05-20-2016, 06:21 AM

In your file example sheet2 cell B4 contains

10.188.104.39 0050569434cb stratsoln-uat-vr/eth2/1.463 VLD 1199 0 0 39

The string to match is 0050569434cb
The string to return is 10.188.104.39

Is the item to return always the 1st string in the cell?
Is the item to match always the 2nd string in the cell?

**Str1der** · 05-20-2016, 06:42 AM

..Correct..

**Richard Buttrey** · 05-20-2016, 08:14 AM

Hi,

Perhaps

Formula:

Please Login or Register  to view this content.

**Str1der** · 05-20-2016, 08:32 AM

It returned the wrong IP - can I ask why B1:B10?

I think because of the full stops in Sheet1 in the values may throw it off?

**Richard Buttrey** · 05-20-2016, 09:04 AM

B1:B10 was merely an example. You would need to adjust the 1:10 range to cover the maximum likely number of rows, or better still use a dynamic range name so that you can forget about how many rows need to be covered.

Upload a workbook with more examples of your data. A single example often isn't enough, particularly if it's not representative of all the permutations of record layouts.

**Kevin#** · 05-20-2016, 09:09 AM

To correct for the full stops
amend the formula and replace both E8 with SUBSTITUTE(E8,".","")

A lazy (but usually effective) way to make the range dynamic is to replace B1:B10 with B:B

**newdoverman** · 05-20-2016, 09:37 AM

Perhaps this will help you. Enter in F8 and fill down as required. If required, change the B:B to the range required.

Formula:

Please Login or Register  to view this content.

	C	D	E	F
7				IP Address (expected value displayed, can it be extracted from sheet 2?)
8	1302 0050.5694.34cb dynamic ip,ipx,assigned,other GigabitEthernet1/4	1302	0050.5694.34cb	10.188.104.39

**AlKey** · 05-20-2016, 11:55 AM

I have replaced your long formula on Sheet1 with a shorter version
Enter in D8 and pull formula to E8

Formula:

Please Login or Register  to view this content.

now for the lookup use this formula in F8 and copy down

Formula:

Please Login or Register  to view this content.

v	B	C	D	E	F
5
6	Formula:
7					IP Address (expected value displayed, can it be extracted from sheet 2?)
8		1302 0050.5694.34cb dynamic ip,ipx,assigned,other GigabitEthernet1/4	1302	0050569434cb	10.188.104.34
9

**Str1der** · 05-23-2016, 09:23 AM

Yep, working perfectly! Thanks

**Str1der** · 05-24-2016, 09:13 AM

Ah, just wondering something... is it possible to replicate exactly the same function but with data in the following format:

? (10.188.229.105) at 00:50:56:94:14:2B [ether] on eth1-01.3808

Might be simpler to covert the data above into the old format, i.e.:

1618 10.188.104.72 00505694639b eth2/1.148 0010dbff6150 1304 0 0x2 0

Extract some data from a string

LinkBack

Thread Tools

Rate This Thread

Display

Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Re: Extract some data from a string

Thread Information

Users Browsing this Thread

Similar Threads

Extract name from a string, not constant data

[SOLVED] Extract data from text string

[SOLVED] Extract data from text string

Extract data from text string

Extract Data from a string

How to extract data from a string?

Extract data from String

Bookmarks

Bookmarks

Posting Permissions