Hi all
I'm trying to extract information out of a text string in a single cell. This one cell contains the User Name, Date Logged on, PC Service Tag, PC Model and last logon location all written in one line. e.g:
Rachel Jones 24/08/2009 JMT9P1J-OptiPlex 330 Location: DERBY
However, the script that builds this single line of text can sometimes get written the wrong way round and ends up like this:
24/08/2009 Rachel Jones JMT9P1J-OptiPlex 330 Location: DERBY
As you can see the Date and the name get switched.
I'm looking for something that will allow me to check which 'format' the descriptions are in so that I can create the right formulas to extract the individual info. (Ultimately I would like to pull User Name, Date Logged on, PC Service Tag, PC Model and last logon location and stick them in their own cell.)
I have used the SEARCH function and wildcards to pick out the name, e.g:
=SEARCH("* ??/??/???? ???????-",C2,1)
This works fine for the 'Name First' type but when it meets the 'Date First' type it displays #VALUE!. I can easily create a second 'mask' to pick up the 'Date First' format but I still end up with a load of #VALUE! around which are causing problems when trying to use the data later. (I guess that's because the SEARCH function can't find the string so it gives this error.)
Is it possible to create a formula that checks to see if it's Format1 or Format2 and returns the value "Format1" or "Format2" (or "Error" if neither are correct)?
Any help is much appreciated.
Brelin
Bookmarks