Text to columns

[Debrap29]

I am having a problem with "Text to Columns". I have a column which includes information which is separated by "/". However, the "/" is not included in every row of the column. i.e. 1 row in the column contains "ADMIN1/232322" and that's easy to make into two columns, however in row 2 I have "Whatever". This information goes into the left column where "ADMIN1" would be, but I need it to go into the right column where "232322" would be.
Is there a way of me getting this information into the correct column?

[crazybass2]

Debra,

Try this....

Sub Text2Column()
Dim MyRange As Range, cell As Range
Set MyRange = Range("A1:A4")
For Each cell In MyRange
If InStr(1, cell, "/") = 0 Then cell = "/" & cell
Next cell
MyRange.TextToColumns DataType:=xlDelimited, other:=True, otherchar:="/"
End Sub

Setting MyRange equal to the range (or column) that you want TextToColumn-ed.

Mike
"Debrap29" wrote:

>
> I am having a problem with "Text to Columns". I have a column which
> includes information which is separated by "/". However, the "/" is not
> included in every row of the column. i.e. 1 row in the column contains
> "ADMIN1/232322" and that's easy to make into two columns, however in
> row 2 I have "Whatever". This information goes into the left column
> where "ADMIN1" would be, but I need it to go into the right column
> where "232322" would be.
> Is there a way of me getting this information into the correct column?
>
>
> --
> Debrap29
> ------------------------------------------------------------------------
> Debrap29's Profile: http://www.excelforum.com/member.php...o&userid=36127
> View this thread: http://www.excelforum.com/showthread...hreadid=559982
>
>

[Dave Peterson]

The answers you got Friday didn't help????????


Debrap29 wrote:
>
> I am having a problem with "Text to Columns". I have a column which
> includes information which is separated by "/". However, the "/" is not
> included in every row of the column. i.e. 1 row in the column contains
> "ADMIN1/232322" and that's easy to make into two columns, however in
> row 2 I have "Whatever". This information goes into the left column
> where "ADMIN1" would be, but I need it to go into the right column
> where "232322" would be.
> Is there a way of me getting this information into the correct column?
>
> --
> Debrap29
> ------------------------------------------------------------------------
> Debrap29's Profile: http://www.excelforum.com/member.php...o&userid=36127
> View this thread: http://www.excelforum.com/showthread...hreadid=559982

--

Dave Peterson

[Debrap29]

Dave:

I had gotten a few answers and tried just about all of them. Not sure which one you sent. The problem is I am EXTREMELY new to Excel, so I believe what you had told me helped for one cell, but not the entire column and since I am so new to Excel, I am not sure how to work the formula you gave me to do that.

Thanks.

[Dave Peterson]

Say your range to fix is in A1:A999
Put that formula in B1 and drag down to B999
(excel will adjust the formula for you for each cell)

Debra Dalgleish goes into more detail here:
http://contextures.com/xlDataEntry01.html

This was my original suggestion:

I'd insert a helper column to the right of your column.

Then use a formula like this:
=IF(COUNTIF(A1,"*/*")=0,"/","")&A1

Then drag this formula down the column.

Then select that new column
edit|copy
edit|paste special|values

And do data|text to columns against this helper column.

And delete the stuff you don't need when you're done.

======
When you have trouble understanding a suggestion, it's usually lots better to
respond within the same thread. The people will know what was suggested and
what you tried without having to guess.

And it's better to post to just one newsgroup. Then you only have to go to that
one newsgroup to find all the suggestions.

Debrap29 wrote:
>
> Dave:
>
> I had gotten a few answers and tried just about all of them. Not sure
> which one you sent. The problem is I am EXTREMELY new to Excel, so I
> believe what you had told me helped for one cell, but not the entire
> column and since I am so new to Excel, I am not sure how to work the
> formula you gave me to do that.
>
> Thanks.
>
> --
> Debrap29
> ------------------------------------------------------------------------
> Debrap29's Profile: http://www.excelforum.com/member.php...o&userid=36127
> View this thread: http://www.excelforum.com/showthread...hreadid=559982

--

Dave Peterson