Search data and String manipulation

[kalle82]

I have data in Rows and columns in excel.

Now the thing is that I want to search this data, in an efficient manner and I think i need to manipulate the strings.

The Purpose of program iself is to give the user the correct phonenumber.

Excel file looks like this
A B C
Unusable data City Phonenumber

D
0 Täljstensvägen 1|5 44301-44305|5 Brevbäring|5 44320-44345|5 Utdelning|5 Brevbärarkontor|5 Stenkullen|5 44311-44315|5 44350-44361|5 44370-44374|5 44380-44396|5 448|5 Floda|5 Gråbo|5 Lerum|5 Meddelande AB

So what I think i need to do is to first manipulate the strings in column D.

The key to the search is the zipcode. Zipcode is written like this 44301-44305 or 10430 or 57701-57702.

So I think I need to let the program know that if i search for 44302, the value is present but inside 44301-44305.

And that a search for 57701 are present in 57701-57702.

When that is done and the strings have the correct form, i need to conduct a search? That finds the zip code and gives med back the phone number from column C

Hell i Dont even know where to start??!

Anyone have any tips?

 

[kalle82]

Update.. I have broken down the strign in column D into many columns based on the separator "|". And i have also replaced the "5", the format of all columns from D and forward are "text".

I have tried different ways to search and as I thought:

In order to find 30571, ill have to create that number from, 30570-30572. How do I do that?

 

[macropod]

Hi kalle82,

The worksheet formulae for both sides of the string would be:
=MID(D1,1,FIND("-",D1)-1)
and
=MID(D1,FIND("-",D1)+1,LEN(D1)-FIND("-",D1))
respectively.
The vba equivalents would be LoVal & HiVal in:

Dim LoVal, HiVal
With ActiveSheet
LoVal = Mid(.Range("D1"), 1, InStr(.Range("D1"), "-") - 1)
HiVal = Mid(.Range("D1"), InStr(.Range("D1"), "-") + 1, Len(.Range("D1")) - InStr(.Range("D1"), "-"))
End With

Cheers
[MS MVP - Word]

 

[SkipVought]

if i search for 44302, the value is present but inside 44301-44305.

What you have is a minimum value and a maximum value. This type of lookup can easily be accomplished on a spreadsheet.

Assuming your string is in F1 and the search value in G1...
[tt]
=AND(LEFT(F1,5)<=G1,RIGHT(F1,5)>=G1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Hi!!

First of all thank your both for your replies... I think i have mislead you all with my bad programming skills...

By using the "text to columns" i have broken down every piece of value into separate cells.

Things now i need to work with them most of the values are straight forward zipcodes like 92050 or 85040, but we have these problematic ones i described before.

Those that are based on an interval like 59030-59034, what i need to do is build a function that goes through my whole spreadsheet. When it finds a string with a "-" i should read the left side as the lower number and the right side as the higher. Then I need it to "IN THE SAME CELL AS FOUND" write the numbers inbetween with an increment of 1.

so the cell with 59030-59034 should be 59030, 59031, 59032, 59033, 59034

When that is done i need to work on my search, the telephone number is always in the C column to the left of the zipcode, so basicly i need the search to go to the C column left of the column it finds the zip code in. the zip code could appear in column D it can also appear in Column AB, still it has it´s correct phone number to the left in the Ccoulmn.

 

[SkipVought]

59030-59034 should be 59030, 59031, 59032, 59033, 59034

WHY? A simple formula, as I posted, can find any value between the min & max!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi kalle82,

As Skip says, why? It is a simple matter to find out whether a given zip code value falls within the range of the left & right values without having to generate all the values in between.

Further, writing out all the values back into the cell from which the range was extracted is hardly going to do anything for readability or making it any easier to test whether the zip code value matches any of those numbers.


Cheers
[MS MVP - Word]

 

[SkipVought]

Yes! Generating all the discrete values is an unnecessary step and it does not produce better results: rather it makes processing much more difficult!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Skip,

I'm not sure about the "makes processing much more difficult" bit - even with all the numbers present, I'd still still test whether the zip code falls within the bounds of the upper & lower numbers in the cell and ignore the other values in the cell.


Cheers
[MS MVP - Word]

 

[kalle82]

Hmm! Thanks again... Im a to nooby programmer to understand everything...

I'm sorry but what im trying to do is to let the user input a value into a textbox in a userform. Then do a search through the file and return the correct phonenumber.

I can't understand how to do this using vba code.

=AND(LEFT(F1,5)<=G1,RIGHT(F1,5)>=G1)

Should I replace F1 with the text the user inserts in the search textbox in my userform, and how do i get G1 to be all cells in the spreadsheet?

 

[SkipVought]

You could use something like this...

Function PhoneNumber(zip As String) As String
    Dim r As Range
    
    PhoneNumber = ""
    
    For Each r In [zip_code_range]
        If Left(r.Value, 5) <= zip And Right(r.Value, 5) >= zip Then
            PhoneNumber = Cells(r.Row, phon_nbr_col).Value
        End If
    Next
End Function

you must supply the zip_code_range & phon_nbr_col

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Skip,

You've omitted the cases where only one zip code exists in a cell. For a fully-fledged function, you'd need something along the lines of:

Function PhoneNumber(Zip As String) As String
Application.Volatile
Dim r As Range, Zip_Rng As Range, Phon_Col As Integer
Phon_Col = # 'Insert the column number here
With ActiveSheet
  Set Zip_Rng = .Range("D1", .Range("D" & .Rows.Count).End(xlUp))
End With
PhoneNumber = "#N/A!"
For Each r In Zip_Rng
  If InStr(r.Value, "-") = 0 Then
    If r.Value = Zip Then Exit For
  ElseIf Left(r.Value, 5) <= Zip Then
    If Right(r.Value, 5) >= Zip Then Exit For
  End If
Next
On Error Resume Next
PhoneNumber = Cells(r.Row, Phon_Col).Value
End Function

I've assumed the Zip Range is Column D, per kalle's posts.


Cheers
[MS MVP - Word]

 

[SkipVought]

Realize that. I was only concerned with prooving that the MIN-MAX was not a problem. Thanz for completing the functionality.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

You've omitted the cases where only one zip code exists in a cell.
I disagree as in this case Left(r.Value, 5) is equal to Right(r.Value, 5)

 

[macropod]

hi PHV,

in this case Left(r.Value, 5) is equal to Right(r.Value, 5)

Ahh, so true. Don't know what I was thinking. We could probably omit the Application.Volatile line as well.


Cheers
[MS MVP - Word]