Formula to slipt data into 3 parts

[tonywatsonhelp]

Hi everyone,
Just had a nightmare download, and need to get this out asap.
hope some can help me
all my data has come in in one cell per row

It looks like this

"127FsFredsmith(257)"

<colgroup><col></colgroup><tbody>
</tbody>

Now what I need is 3 columns

"127" "Fs" "Fredsmith(257)"
so the first part is always a number, could be any number,
the second Always starts with a Capital and ends at the Next Capital, will always be letters and only ever 1 or 2 long, then everything else left
please help if you can,
formula or VBA its all in column A at the mo.

Thanks
Tony

 

[tyija1995]

Hi Tony,

Got somewhat of a fix, but the assumption I have made is that the "Fs" part is always 2 characters long... I'll try fix this but try this in the mean time:
Define the following UDF:

Function NumberSeparator(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        NumberSeparator = .Replace(txt, ".")
    End With
End Function

Then assuming "127FsFredSmith(257)" is in cell A1 then try these 3 formula's in B1, C1 & D1:
B1: =LEFT(NumberSeparator(A1),SEARCH(".",NumberSeparator(A1))-1)
C1: =MID(A1,SEARCH(".",NumberSeparator(A1)),2)
D1: =MID(A1,LEN(B1)+LEN(C1)+1,100)

 

[FormR]

Here's another option, note that they rely on each other to return the correct result.

Excel 2013/2016
	A	B	C	D
1	127FsFredsmith(257)	127	Fs	Fredsmith(257)
2	12345678GFfdsfmds 1ds1a2	12345678	G	Ffdsfmds 1ds1a2
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),$A1),1)-1)
C1	=MID(A1,LEN(B1)+1,AGGREGATE(15,6,FIND(CHAR(ROW(INDIRECT("65:90"))),MID($A1,LEN(B1)+2,LEN(A1))),1))
D1	=MID(A1,LEN(B1)+LEN(C1)+1,LEN(A1))

 

[tyija1995]

Tried a similar but slightly different approach to my first solution which seems to work (if you are interested in using regular expressions with VBA) then you can also try this formula:

Function CountCapitals(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[A-Z]"
        CountCapitals = .Replace(txt, "#")
    End With
End Function

B1:
=LEFT(CountCapitals(A1),SEARCH("#",CountCapitals(A1))-1)

C1:
=SUBSTITUTE(MID(CountCapitals(A1),LEN(B1)+1,SEARCH("#",MID(CountCapitals(A1),LEN(B1)+1,100),2)-1),"#",MID(A1,LEN(B1)+1,1))

D1:
=SUBSTITUTE(MID(CountCapitals(A1),LEN(B1)+LEN(C1)+1,100),"#",MID(A1,LEN(B1)+LEN(C1)+1,1))

Where A1 is the text extracted e.g. 127FsFredsmith(257)
B1 will show: 127
C1 will show: Fs
D1 will show: Fredsmith(257)

 

[tonywatsonhelp]

FormR Thank you, worked great,
Tyija, great idea, i love the functions as they can be used in so many different ways, thank you
Big thank you for this i am in time for my 12 noon deadline
Tony