How do I insert an underscore between two alpha characters using VBA.

[ijswalker]

Hi,

I have a file that has a field that contains both company name and numeric data. I have tried parsing the data in excel but the company name has multiple words with each word separated by a space. I cannot figure out how to replace the spaces between the company name words with an underscore. I have been trying to add an underscore between two alpha characters. I think that would work and then I could parse it properly from there. Is there a VBA solution to this and also to parsing the data?

Can anyone help.

Thanks

Ian

 

[RuralGuy]

Have you tried Replace([YourField]," ","_")?

 

[ijswalker]

Hi,

Yes, unfortunately I only want to replace the spaces with the underscore where there is an Alpha space Alpha. Here is a sample of the data.

2261-0599 1,004.000 COCA COLA COMPANY 10/23/2007 -$43,056.03

The above data is all in one field. If I parse it the

COCA COLA COMPANY

is split into three cells. The problem is that I have over 5,000 rows with varying words and characters. The common link is that only alpha space alpha needs to be replaced by Alpha underscore Alpha. I will then be able to parse thae data and get it into neat columns.

Does that make sense?

Thanks

Ian

 

[RuralGuy]

Is the data you want *always* 2 spaces in from either end?

 

[ijswalker]

From the left yes but from the right it can vary. However, there is always a date after the company name, if that's any help.

 

[ChrisO]

I haven’t tested this in Excel but it is case insensitive so it might work: -

Option Compare Binary
Option Explicit


Sub TestIt()

    MsgBox ReplaceIt("2261-0599 1,004.000 COCa COLA COMPANY 10/23/2007 -$43,056.03")

End Sub


Public Function ReplaceIt(ByVal strLine As String) As String
    Dim lngPos As Long
    
    For lngPos = 2 To Len(strLine) - 2
        If UCase(Mid$(strLine, lngPos, 3)) Like "[A-Z] [A-Z]" Then
            Mid$(strLine, lngPos + 1, 3) = "_"
        End If
    Next lngPos
    
    ReplaceIt = strLine

End Function

Hope that helps.

Regards,
Chris.

 

[MarkK]

ChrisO: I think that's pretty clever.

 

[georgedwilkinson]

Yeah that's pretty slick. A clever way of looking at the problem.

 

[ChrisO]

Thanks Lagbolt and George.

Let’s just hope it works for the OP as well.

Regards,
Chris.

 

[ijswalker]

Thanks Chris,

I'll gave it a whirl.

Cheesr

Ian

 

[ijswalker]

Hi Chris,

That is pretty awesome. I have put this in a module in my database. How would I apply this to each row of my database table? The table is called Test1. I am running Access 2003

Thanks

Ian

 

[ChrisO]

Well Ian I’ve had to make a few guesses here.

There’s a small A2K3 demo attached that should get you started.

Regards,
Chris.

 

[ChrisO]

Attached is an A2K3 version which is a better test, it uses the company names from the Northwind Traders database.

Regards,
Chris.

 

[ijswalker]

Thanks Chris,

That helps. You have saved me a boat load of time now and every month after that.

Cheers

Ian