UDF to Split Text String

I am trying to take data from a table in a pdf. and transfer it to an excel spreadsheet that I will then add to a GIS database. Two different types of data are presented in 2 different formats. I need to remove unnecessary data from each format while maintaining the necessary data and splitting the necessary data into separate columns. Here are the two examples:

1) This may be easier because the two pieces of data I need are always the first and last pieces of data. In some cases there is more data in the cell than others including other numbers. I have included the two primary examples of how it appears below along with examples of how the data should appear afterwards.

(Data in 2 rows/1column)
BP Borrow pits 72.00
FuB Fuquay fine sand, 0 to 6 percent slopes 114.00

(Remaining data in 2 rows/2 columns, Text and numbers in different columns)
BP 72
FuB 114

So you can see I'm splitting BP and 72 into separate columns and deleting "Borrow pits". In the second example I'm splitting FuB and 114 into separate columns and deleting "Fuquay fine sand, 0 to 6 percent slopes".

2) In this one I need to do the same but the placement and type of data is different. The first part is the same. I need to retain the code at the beginning of the string in the 1st column. In the second column I just need whether it is Very Limited, Somewhat Limited, or Not Limited. Here are three examples of data strings. Each would be in a single cell.

(Data in 3 rows/1 column)
BP Borrow pits Very limited Udorthents 0.9
BrA Brogdon loamy sand, 0 to 2 percent slopes Not limited Brogdon 1
Ca Cantey loam Somewhat limited Cantey 1

Here's how the resulting data would look:

(Remaining data in 3 rows/2 columns, data split between code and very, not or somewhat limited)

BP Very Limited
BrA Not Limited
Ca Somewhat Limited

Note: I could use a numeric code (1,2,3) for Very Limited, Not Limited, and Somewhat Limited as I'll eventually have to convert it to that anyway if that makes it any easier.

Thanks in advance for any help you can provide.