Data parsing Excel cells

mmmcdee
mmmcdee used Ask the Experts™
on
I am trying to parse through cells that  -
1 - Whenever a number is followed by a letter;  split apart.  
2 - Whenever a letter is immediately followed by a number; split apart.

 I have a formula in the attached Excel Doc - although I cannot get it to run properly.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Sorry about that -
Book7.xlsx
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Would you settle for VBA code, and if so given your text in A5, what do you want to see in B5?
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
That is Exactly what I need.  I think my formula path is the wrong direction.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What should B5 look like?

Author

Commented:
B5 would be the split apart parsed data from A5.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I understand that you want to parse the data. Please SHOW me what it should look like.

Author

Commented:
I highlighted the changes in B5 on the attached spreadsheet - how the parsing would change A5 to B5.

1 - Whenever a number is followed by a letter;  split apart
2 -  Whenever a letter is immediately followed by a number; split apart.

The coloring s for reference only of course.
Book7.xlsx
Use this function

=NUMCHARSPC(A4)

with this code

Function numcharspc(x)
Dim i As Integer
For i = Len(x) To 2 Step -1
If (Mid(x, i, 1) >= "0" And Mid(x, i, 1) <= "9" And ((Mid(x, i - 1, 1) >= "A" And Mid(x, i - 1, 1) <= "Z") Or (Mid(x, i - 1, 1) >= "a" And Mid(x, i - 1, 1) <= "z"))) Or _
    (Mid(x, i - 1, 1) >= "0" And Mid(x, i - 1, 1) <= "9" And ((Mid(x, i, 1) >= "A" And Mid(x, i, 1) <= "Z") Or (Mid(x, i, 1) >= "a" And Mid(x, i, 1) < "z"))) Then
    x = WorksheetFunction.Replace(x, i, 0, " ")
    End If
Next i
numcharspc = x
End Function

Open in new window

Author

Commented:
PERFECT!  Thank You!
Top Expert 2010

Commented:
mmmcdee,

You state:

1 - Whenever a number is followed by a letter;  split apart.  
2 - Whenever a letter is immediately followed by a number; split apart.

On that basis, please explain why for your sample value:

Red 4 door FWD Sedan 4-Speed Automatic 3.8L V6 12V MPFI OHV Stock# B29954A.

your sample answer does NOT put spaces between V & 6, 12 & V, or B & 29954 & A.

Patrick

Author

Commented:
Patrick -
Apologies on my first Book7.xlsx.  The second posting within this thread had the spaces I was looking for.  

A Side Note:  I posted another question pertaining to 'removing' white space - this one is tough. see:  Removing White Space Between Numbers in Excel

Thanks -
M

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial