I've been struggling with this one for a day. The following data is is in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or more
spaces to the row right above it and tacked on to the data in that row and
then remove 20 of the spaces off the beginning of the data that was moved .
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

If the data to be moved has anywhere from 3 to 19 leading spaces, how can 20
leading spaces be removed? Or are you saying that if it has as much as 3,
then it must have 20+ leading spaces? Please post back and clarify. HTH
Otto
"Dennis" wrote in message
. com...
I've been struggling with this one for a day. The following data is is in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or
more
spaces to the row right above it and tacked on to the data in that row and
then remove 20 of the spaces off the beginning of the data that was moved
.
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

Yes Otto, the rows that have at last 3 leading spaces will always have more
than 20 leading spaces. Thanx and TIA......Dennis

In article , "Otto Moehrbach"
wrote:
If the data to be moved has anywhere from 3 to 19 leading spaces, how can 20
leading spaces be removed? Or are you saying that if it has as much as 3,
then it must have 20+ leading spaces? Please post back and clarify. HTH
Otto
"Dennis" wrote in message
.com...
I've been struggling with this one for a day. The following data is is in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or
more
spaces to the row right above it and tacked on to the data in that row and
then remove 20 of the spaces off the beginning of the data that was moved
.
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

Dennis
The following macro will do what you asked for.
As written, this macro will work on the active sheet.
I assumed that your data is in Column A starting with A2. I assumed A1 is a
header.
Note that there is no error trapping in this macro. You may need or want
some error trapping. For instance, as written, this macro checks for an
entry in Column A that has 3 leading spaces. That entry, when found, will
then be changed by removing the first 20 characters (a space is a
character). What's left will then be appended to the contents of the cell
above it. Obviously, if the entry has less that 20 leading spaces, you will
lose some data.
Note that the entire row of any entry found to have 3 leading spaces will be
deleted by this macro after the contents are appended to the cell above it.
I highly recommend that you make a copy of your file and run this macro in
that copy first.
I am sending you a small file that has this macro properly placed and
working. I am sending this to the email address that is attached to your
post. If this is not a valid email address, email me and give me a valid
email address if you want this file. My email address is
. Remove the "nop" from this address. HTH Otto
Sub MoveData()
Dim RngA As Range 'Range of Col A from A2 down
Dim c As Long
Dim Spaces3 As String 'Entry with =3 leading spaces -20 leading
characters
Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngA.Count - 1 To 1 Step -1
If Left(RngA(c).Offset(1), 3) = " " Then
Spaces3 = Right(RngA(c).Offset(1), Len(RngA(c).Offset(1)) - 20)
RngA(c).Value = RngA(c).Value & Spaces3
RngA(c).Offset(1).EntireRow.Delete
End If
Next c
End Sub
"Dennis" wrote in message
. com...
Yes Otto, the rows that have at last 3 leading spaces will always have
more
than 20 leading spaces. Thanx and TIA......Dennis

In article , "Otto Moehrbach"
wrote:
If the data to be moved has anywhere from 3 to 19 leading spaces, how can
20
leading spaces be removed? Or are you saying that if it has as much as 3,
then it must have 20+ leading spaces? Please post back and clarify. HTH
Otto
"Dennis" wrote in message
y.com...
I've been struggling with this one for a day. The following data is is
in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or
more
spaces to the row right above it and tacked on to the data in that row
and
then remove 20 of the spaces off the beginning of the data that was
moved
.
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it
finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

Otto...thank you!!!!!!!!!!!!!!!! This did exactly what I was looking for and
written very lean. Thanx again!!!

Fixed my email :)

Dennis
=================

In article , "Otto Moehrbach"
wrote:

Sub MoveData()
Dim RngA As Range 'Range of Col A from A2 down
Dim c As Long
Dim Spaces3 As String 'Entry with =3 leading spaces -20 leading
characters
Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngA.Count - 1 To 1 Step -1
If Left(RngA(c).Offset(1), 3) = " " Then
Spaces3 = Right(RngA(c).Offset(1), Len(RngA(c).Offset(1)) - 20)
RngA(c).Value = RngA(c).Value & Spaces3
RngA(c).Offset(1).EntireRow.Delete
End If
Next c
End Sub

Dennis
The email address in your post is not a valid email address. Otto
"Dennis" wrote in message
. com...
Yes Otto, the rows that have at last 3 leading spaces will always have
more
than 20 leading spaces. Thanx and TIA......Dennis

In article , "Otto Moehrbach"
wrote:
If the data to be moved has anywhere from 3 to 19 leading spaces, how can
20
leading spaces be removed? Or are you saying that if it has as much as 3,
then it must have 20+ leading spaces? Please post back and clarify. HTH
Otto
"Dennis" wrote in message
y.com...
I've been struggling with this one for a day. The following data is is
in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or
more
spaces to the row right above it and tacked on to the data in that row
and
then remove 20 of the spaces off the beginning of the data that was
moved
.
After this is done the following would be the result and in column A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it
finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================

Dennis
You should never display your real email address in a newsgroup posting.
The spammers use computers to "harvest" email addresses from newsgroups by
the thousands and then send junk for ever. You should display a fake
address as I do and then include an explanation for someone you want an
email from as to how to convert the fake to a real email address. Because
the spammers use machines, they don't pick up on the explanation. HTH
Otto
"Otto Moehrbach" wrote in message
...
Dennis
The email address in your post is not a valid email address. Otto
"Dennis" wrote in message
. com...
Yes Otto, the rows that have at last 3 leading spaces will always have
more
than 20 leading spaces. Thanx and TIA......Dennis

In article , "Otto Moehrbach"
wrote:
If the data to be moved has anywhere from 3 to 19 leading spaces, how can
20
leading spaces be removed? Or are you saying that if it has as much as
3,
then it must have 20+ leading spaces? Please post back and clarify.
HTH
Otto
"Dennis" wrote in message
gy.com...
I've been struggling with this one for a day. The following data is is
in
Column A.

(GOLD)JOB/402/ATTBYE
DBDATA 30450 30

What I'm trying to to accomplish is to move data that starts with 3 or
more
spaces to the row right above it and tacked on to the data in that row
and
then remove 20 of the spaces off the beginning of the data that was
moved
.
After this is done the following would be the result and in column
A....

(GOLD)JOB/402/ATTBY DBDATA 30450 3

I have about 20,000 rows of data to go thru and have processed if it
finds
data that starts with more than 3 spaces in Column A.

TIA....Dennis
===================