Prev: VBA macro for reading text file into Excel
Next: Excel 2007 is stuck in Document recovery mode
From: James on 15 Oct 2009 12:08
The format that another application spits out is one that I would like to
make user friendly. Here is an example of what I am looking at:
WELL: 5122110000 3096971.9998
PICK: GRETA
**Each line of data is in one cell

In my normal file I have anywhere between 400 to 2000 rows of data. I would
like something that will take the lines that have "PICK:" and place all that
information on the row above it and paste that line of data after the end of
the data in the row "WELL:"

So what this line would look like would be:
WELL: 5122110000 3096971.9998 PICK: GRETA

When it puts the line with "PICK:" it would be great if there was a space
between the last value in the line with the "WELL:" and the P in "PICK:"

Thanks for the help
From: Dave Peterson on 15 Oct 2009 12:40
So you want all that stuff in a single cell?

If yes, then put this in B1:
=A1&" "&A2
Put this in B2:
=na()

Select B1:B2 and drag down as far as you need.

Select column B
Edit|Copy
Edit|Paste special|Values

With column B still selected
Edit|Goto (or hit F5)|Special|check Constants and errors (uncheck the rest)

Rightclick on any of those errors
Delete|shift cells up



James wrote:
>
> The format that another application spits out is one that I would like to
> make user friendly. Here is an example of what I am looking at:
> WELL: 5122110000 3096971.9998
> PICK: GRETA
> **Each line of data is in one cell
>
> In my normal file I have anywhere between 400 to 2000 rows of data. I would
> like something that will take the lines that have "PICK:" and place all that
> information on the row above it and paste that line of data after the end of
> the data in the row "WELL:"
>
> So what this line would look like would be:
> WELL: 5122110000 3096971.9998 PICK: GRETA
>
> When it puts the line with "PICK:" it would be great if there was a space
> between the last value in the line with the "WELL:" and the P in "PICK:"
>
> Thanks for the help

--

Dave Peterson
 | 
Pages: 1
Prev: VBA macro for reading text file into Excel
Next: Excel 2007 is stuck in Document recovery mode