Trying to extract data from cells...

[rob5497]

Hi, I have some data that has imported from a finance system in a dreadful format that is inconsistent from one record to another and I'm after some guidance on how to extract the data that I need from each please. Tried MID, LEN, RIGHT, LEFT and many combos of all of those but I can't get it right...

For example, here is the data record (I have +2000 records like this, all different):

[TABLE="width: 500"]
<tbody>[TR]
[TD]0.14, LASTNAME FIRSTNAME, W/E 09/04/2017, ITU, 23.00 HRS, 17003967

What I would like to do is extract the following data, but I am finding it difficult because of the differing lengths:

- The name of the person
- the date (minus the "w/e" leading text)
- the department (as in the above example it is ITU, but could be anything)
- the number of hours (these could be from 1 to 5 digits long, but most (not all) records do contain the "HRS" designation)

It's almost like I need a formula to look at the data between each "," - but I have no idea how to.

Is there anything I can do to also remove the leading "X.XX" (0.14 above) and also the trailing "XXXXXXXX" (17003967 above) from each record?

Thanks so much in advance,

Best regards

Rob

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

One thing that may help you in your need is to separate your data into multiple columns based on the commas.
You can do this pretty easily using "Text to Columns" (found under the Data menu).
If you select your column, and go to "Text to Columns", you will want to choose the "Delimited" option with the commas as your delimiter.

Once you get the different data items into their own separate columns, it should be much easier to use other functions (like LEFT, RIGHT, MID) to get what you want.
Give it a try, see how far you get, and post back here with any problems.

 

[rob5497]

Thank you Joe, I will give that a go. Appreciate the guidance.

Rob

 

[Rick Rothstein]

If you are willing to consider a VBA macro, here is one that should work. Note, though, that the code assumes your data is in Column A and that it starts on Row 1 (if that assumption is wrong, tell me where the data actually is and I'll modify the code accordingly)...

[table="width: 500"]
[tr]
	[td]Sub FixAndSplitApartRecords()
  Dim R As Long, Txt As String, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Replace(Trim(Mid(Left(Data(R, 1), InStrRev(Data(R, 1), ",") - 1), InStr(Data(R, 1), ",") + 1)), "HRS", "", , , vbTextCompare), ", ", ",")
  Next
  Range("A1").Resize(UBound(Data)) = Data
  Columns("A").TextToColumns Range("B1"), xlDelimited, , , False, False, True, False, False
  Columns("A:E").AutoFit
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FixAndSplitApartRecords) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jtakw]

For example, here is the data record (I have +2000 records like this, all different):

[TABLE="width: 500"]
<tbody>[TR]
[TD]0.14, LASTNAME FIRSTNAME, W/E 09/04/2017, ITU, 23.00 HRS, 17003967

[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

When you say "all different", what do you mean? Do you mean the format of the text strings are all different or just the data?

May be you can give more than just One sample?

 

[rob5497]

Thank you Rick, that is marvellous and just the job. It's has saved me a lot of time Still a lot of records that don't meet any of the criteria (lazy data input!) so had to do some manual correction, but nowhere near the 2000+ I started with.

Thanks again, I appreciate your help.

Rob

 

[rob5497]

Hi jtakw,

It was mainly the length of the data was different, particularly the last set of numbers etc. Rick's solution sorted it wonderfully.

Best regards

Rob

 

[Rick Rothstein]

Still a lot of records that don't meet any of the criteria (lazy data input!) so had to do some manual correction

Can you describe what this "lazy data input" looks like (are we just talking about extra or irregular spacing)... perhaps I can modify the code to account for it.

 

[rob5497]

Thank you Rick. It is too complicated to mention or even fathom, so I ended up going through it line by line. Thank you for your offer of further support, I appreciate it.

Best wishes

Rob