copy part of Excel cell to other cell

[HansD]

Hey People

i've got a little problem with sorting data
I need to import a lot of data from a internetsite in excel

a cell looks like this:
121212 name of the article 12,00

I need to split this in three cells
cell 1: 121212
cell 2: name of the article
cell 3: 12,00

I really hope somebody can help me

Thanks
Hans

 

[SkipVought]

Hans

Is it always 6 characters, article name (varying length), 5 characters?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HansD]

Hey Skip

Thanks for your really fast reply.

The first is until now still 6 characters, but the last is a price it can also be 123,00 or 1100,00

Hans

 

[SkipVought]

Copy this procedure to a VBA Module alt+F11 opend the VBA Editor)

select you cells to parse and run from Tools/Macro/Macros...

Sub Parse4Hans()
'select the cells to parse and run this macro
    Dim s As Range, i As Byte, t As String, a
    For Each s In Selection
        i = 0
        t = ""
        For Each a In Split(s)
            Select Case i
                Case LBound(Split(s), 1)
                    Cells(s.Row, s.Column + 1).Value = a
                Case UBound(Split(s), 1)
                    Cells(s.Row, s.Column + 2).Value = t
                    Cells(s.Row, s.Column + 3).Value = a
                Case Else
                    t = t & a
            End Select
            i = i + 1
        Next
    Next
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HansD]

Thanks man

I am going to try it out
I will let you know if it works

 

[HansD]

It is a really good code, I thought it was going to be much longer GOOD WORK!!!

Only one thing goes wrong:

121212 name of the article 12,00

cell 1: 121212 name
cell 2: of the article
cell 3: 12,00

cell 1 goes to the second space

 

[SkipVought]

Just change the

Cells(s.Row, s.Column + ?)

from

1, 2 & 3

to

0, 1 & 2



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HansD]

Hey

I am glad that you reply so fast, thanks

But the 1,2&3 are good
When you change those to 0,1&2 the first case overwrites the original data.

i need to split the sentece in three cells articlenumber, article and prize

Your code is great but I really don't understand it (hahaha)so i don't know what to change

the article number now is:
"1212121 name" and not "1212121"

The word "name" needs to go in the article cell

Hans

 

[SkipVought]

apparently there was no space between the article number and the article name

Needs to be a space between

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HansD]

There is a space.

This is an example of a sentence:
11005 SUNBEAM HDS 5.25" HDD 3.5" SILENCER RACK-- 8.55

after i run the code i get:
cell 1: 11005 SUNBEAM
cell 2: HDS 5.25" HDD 3.5" SILENCER RACK--
cell 3: 8.55

By the way, is it possible to lose the -- at the end of the articlename. The problem with that is that there are not allways 2 stripes sometimes 3 or 4

Thanks again for the fast reply

Hans

 

[HansD]

Could this be the problem:

There is something missing in the code you publiced in your first reply

Dim s As Range, i As Byte, t As String, a

I think there must be something behind the a


Hans

 

[SkipVought]

1. looks like a space but I'd wager it not. Edit that cell, delete whatever it is and insert a space. Then try that again.

2. Select the entire column - Edit/Replace "-" with ""

3. the variable, a is a Varient by default -- OK!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HansD]

That's stange, when i delete the space after the articlenumber and put it there again, your code works

there is something else (OH NO NOT SOMETHING ELSE!!!...sorry)
your code deletes the spaces in the article description


Sorry about all my questions, but you are really helping me good


Hans