eemoon
asked on
How to transform one row line like this in excel table 2010?
Hi
Here is one row data like this:
aa bb cc dd ee . . . . . .
These five data in five cells, respectively.
I hope I can copy them into notepad like this:
aa,bb,cc,dd,ee
If I directly copy it into notepad and replace the spaces with comma, it does not work
Anyone can give some suggestion? Thank you in advance
Here is one row data like this:
aa bb cc dd ee . . . . . .
These five data in five cells, respectively.
I hope I can copy them into notepad like this:
aa,bb,cc,dd,ee
If I directly copy it into notepad and replace the spaces with comma, it does not work
Anyone can give some suggestion? Thank you in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for your fast reply. it can work, but these data are much longer
ASKER
it is more than 100
did you try saving a copy of the excel file as .CSV ? as suggested by Konrad
ASKER
Can you create longer one than this?
=A2 & "," & B2 & "," & C2 & "," & D2 & "," & E2
=A2 & "," & B2 & "," & C2 & "," & D2 & "," & E2
you can use vba
dim j as integer , str as string
For j = 65 To 165 Step 1
If j < 91 Then
str = str & Chr(j) & 2 & Chr(38) & """,""" & Chr(38)
ElseIf j < 117 Then
str = str & "A" & Chr(j - 26) & 2 & Chr(38) & """,""" & Chr(38)
ElseIf j < 143 Then
str = str & "B" & Chr(j - 52) & 2 & Chr(38) & """,""" & Chr(38)
ElseIf j < 165 Then
str = str & "C" & Chr(j - 78) & 2 & Chr(38) & """,""" & Chr(38)
Else
str = str & "C" & Chr(j - 78) & 2
End If
Next
str = "=" & str
Debug.Print str
ASKER
Thanks
Try to copy character from between aa and bb (after pasting everything into notepad) and paste it into replace box, then replace with commas.
You can also save excel file as a csv and then open it using notepad (it should be semicolon separated).