Find Numeric Text in Cell 1

[CurtR]

Hi Everyone,
I have this problem. I was given a 17000 record report that needs to be disected.
I have the data in Excel and have using mid and Find functions gotten most of what I need.
One last problem I am having is getting a street address from this jumbled mess.
Here is a snap shot of the data.
"01-0120.300 STERLING PROP ERTI ES 36 WEST AVE 105 80 14 23.7"

Now part of the problem is this. While what you see here is broken by spaces.What I see and Excel sees( due to the way that data came across) is the little square space Character. Because the number of spaces is not the same in every record. I do not know how to search to find the proper one beore the "36" in this case.
I need to extract 36 WEST AVE from this example.
Is there a way to search/ find this leading number?
Thanks
Curt

 

[dneufarth]

The square is usually the CR/LF (carriage return or line feed) character(s). Are there always a consistent number of these before the address?

If so, you should be able to loop through the record until finding the N th occurrence and take all characters until the next occurrence using VBA code.


Dave

 

[CurtR]

No the number of these little devils is not always the same. Wish they were!
It would be a simple matter at that point.

I was hoping to be able to find the first numeric and then go out to the next instance of this character/ space to end the address that I need.
Thanks
Curt

 

[DaleWatson123321]

Curt,

I've got some GOOD news for you... It took SOME doing, but I put together a solution. It involves several formulas - which are too "involved" to cover here, so I would ask you to email me and I'll send the file via return email.

The file has GOOD documentation so should be easy to follow, and to use.

In addition, I've displayed the formulas in both a "vertical orientation" - for ease of understanding the "process" - and on a separate sheet they are in a "horizontal orientation" which you'll require for conversion of your data.

If anyone else is interested, you're also welcome to a copy of the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Ya know, I usually try to settle this using Word. (Am a Word person at heart.)

Copy and paste your cells right into Word. Generally, they paste right into a table format.

Hit the show/hide button--it looks like a backwards P on the upper toolbar. This allows you to view non-printing characters.

Copy one of those characters.
Hit Ctrl-h to bring up Find/Replace.
Click in Find and hit ctrl-v to paste that character.
Don't put anything in the replace with box.
Hit replace all.

Click inside the table. Hit Table-Select table.
Copy it.
Open Excel, paste.

I've been able to accomplish this stuff with just one column of Excel data and pasting that column right back into Excel.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[mscallisto]

If you still haven't resolved your problem could you paste a piece of the original data in this thread so we can take a better look at it. The example you submitted seemed not to have cf/lf's or other delimeters between fields.

 

[CurtR]

Dalewatson123321 came up with a sheet that counted and seperated the instances of the carraige return character.
I have tried to copy and paste that character here, but as in the original post, it shows as only a space.
Something that we noticed while doing this. If I took the line of text and copied it from a cell to another cell, simply using paste. It would recreate the exact line of text. If however I were to copy the line of text by high lighting it in the formula bar and then pasting it into an empty cell it would actually break the text out into different cells on those carraige return characters.
Dale can explain what he did but he used several instances of the mid and find functions to find each instance of the character and then it's place in each line of text.

Thanks to everyone and to Dale for the Help.
Curt