By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,644 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,644 IT Pros & Developers. It's quick & easy.

Copying/Pasting Text from/to Cells

P: 9
Hello There,
I am trying to write a macro that will copy part of Text in a Cell & then paste in another cell.
For example,
assume cell A1="ABE_BC1 AbTS 11 bisTSport 2", I would like to copy/Paste "11" in cell A2 & 2 IN cell A3 .How do i achieve this with a macro instead?
Can anyone help?

CELL A1 = ADDRESS CELL B1 = BOARD CELL C1 = SLOT
CELL A2 = ABE_BC1 AbTS 11 bisTSport 2 CELL B2 = 11 CELL C2 = 12
CELL A3 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B3 = 11 CELL C3 = 3
CELL A4 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B4 = 11 CELL C4 = 3
CELL A5 = ABE_BC1 |AbTS 14 AbTSport 4 CELL B5 = 14 CELL C5 = 4
CELL A6 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B6 = 2 CELL C6= 5
CELL A7 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B7 = 2 CELL C7 = 5
Nov 1 '06 #1
Share this Question
Share on Google+
5 Replies


Expert 5K+
P: 8,434
Hello There,
I am trying to write a macro that will copy part of Text in a Cell & then paste in another cell.
For example,
assume cell A1="ABE_BC1 AbTS 11 bisTSport 2", I would like to copy/Paste "11" in cell A2 & 2 IN cell A3 .How do i achieve this with a macro instead?
Can anyone help?

CELL A1 = ADDRESS CELL B1 = BOARD CELL C1 = SLOT
CELL A2 = ABE_BC1 AbTS 11 bisTSport 2 CELL B2 = 11 CELL C2 = 12
CELL A3 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B3 = 11 CELL C3 = 3
CELL A4 = ABE_BC1 |AbTS 11 AbTSport 3 CELL B4 = 11 CELL C4 = 3
CELL A5 = ABE_BC1 |AbTS 14 AbTSport 4 CELL B5 = 14 CELL C5 = 4
CELL A6 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B6 = 2 CELL C6= 5
CELL A7 = UMH_BC1 |AbTS 2 AbTSport 5 CELL B7 = 2 CELL C7 = 5
Try this.
Expand|Select|Wrap|Line Numbers
  1. Sub DoIt()
  2.   Dim I As Long
  3.   For I = 2 To 7
  4.     Cells(I, 2).Value = Word(3, Cells(I, 1).Value)
  5.     Cells(I, 3).Value = Word(5, Cells(I, 1).Value)
  6.   Next
  7. End Sub
  8.  
  9.  
  10. Private Function Word(DesiredWord As Long, FromString As String) As String
  11.   Dim I As Long, J As Long, Char As String * 1
  12.   Dim WordNum As Long, StartPos As Long, NextSpace As Long
  13.   Dim WhatWasFound As String
  14.   StartPos = 1
  15.   If FromString = "" Then Exit Function
  16.   Do
  17.     WordNum = WordNum + 1
  18.     NextSpace = InStr(StartPos, FromString, " ")
  19.     If NextSpace = 0 Then
  20.       If WordNum = DesiredWord Then
  21.         WhatWasFound = Mid$(FromString, StartPos)
  22.       End If
  23.       Exit Do
  24.     End If
  25.     If WordNum = DesiredWord Then
  26.       WhatWasFound = Mid$(FromString, StartPos, NextSpace - StartPos)
  27.       Exit Do
  28.     End If
  29.     StartPos = NextSpace + 1
  30.   Loop
  31.   Word = Trim$(WhatWasFound)
  32. End Function
  33.  
I have made a lot of assumptions, of course. But I tried to make it flexible where possible. For one thing, the Word() function lets you specify that you want the 1st word, 2nd word or whatever, from the input string. This may not match what you want to do.

My results...
Before
Expand|Select|Wrap|Line Numbers
  1. ADDRESS                           BOARD   SLOT
  2. ABE_BC1 AbTS 11 bisTSport 2              
  3. ABE_BC1 |AbTS 11 AbTSport 3              
  4. ABE_BC1 |AbTS 11 AbTSport 3              
  5. ABE_BC1 |AbTS 14 AbTSport 4              
  6. UMH_BC1 |AbTS 2 AbTSport 5               
  7. UMH_BC1 |AbTS 2 AbTSport 5               
After
Expand|Select|Wrap|Line Numbers
  1. ADDRESS                           BOARD   SLOT
  2. ABE_BC1 AbTS 11 bisTSport 2     11      2
  3. ABE_BC1 |AbTS 11 AbTSport 3     11      3
  4. ABE_BC1 |AbTS 11 AbTSport 3     11      3
  5. ABE_BC1 |AbTS 14 AbTSport 4     14      4
  6. UMH_BC1 |AbTS 2 AbTSport 5      2       5
  7. UMH_BC1 |AbTS 2 AbTSport 5      2       5
Nov 2 '06 #2

Expert 5K+
P: 8,434
P.S. One of the assumption I made is that the 12 in C2 was a typo.
Nov 2 '06 #3

P: 9
P.S. One of the assumption I made is that the 12 in C2 was a typo.
Thank you, The code works.
But i would like to know what the arguement (DesiredWord) in the Function (Word)
is for.

Private Function Word(DesiredWord As Long, FromString As String) As String
Nov 2 '06 #4

Expert 5K+
P: 8,434
Thank you, The code works.
But i would like to know what the arguement (DesiredWord) in the Function (Word)
is for.
Private Function Word(DesiredWord As Long, FromString As String) As String
You use that to say which word you want to pull out of the string. If I remember correctly, in the example I passed it "3" and "5" to extract the 3rd and 5th words. Try passing different numbers, to see what you get.
Nov 2 '06 #5

P: 9
You use that to say which word you want to pull out of the string. If I remember correctly, in the example I passed it "3" and "5" to extract the 3rd and 5th words. Try passing different numbers, to see what you get.
Thanks for your help
Nov 3 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.