Extracting characters from right of a column using vba

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
61
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello guys,
i am stuck in this, did few searches but not able to find solution to this simple problem.

I want to extract 25 characters from Right of a column.
My data starts from, let's say B2 till end (End(xlDown)) .


how can i achieve this using simple VBA code.


My data got Unicode characters + symbols in it, i believe that doesn't make any difference.


So basically i want Right (B2,25) but using VBA for whole column till the last record.



Sample table:-

A
1Description
2PEF Invoice No. 61724556 Ref. CPS
3PEF Invoice No. 61738222 Ref. CPS
4PEF Invoice No. 61798350 Ref. CPS
5PEF Invoice No. 61804695 Ref. CPS
6PEF Invoice No. 61882858 Ref. CPS
7PEF Invoice No. 61781054 Ref. CPS
8PEF Invoice No. 65047248 Ref. CPS
9PEF Invoice No. 67007403 Ref. CPS
10PEF Invoice No. 61909416 Ref. CPS
11PEF Invoice No. 61873772 Ref. CPS
12PEF Invoice No. 61748818 Ref. CPS
13PEF Invoice No. 61763096 Ref. CPS
14PEF Invoice No. 61760588 Ref. CPS
15PEF Invoice No. 61886842 Ref. CPS
16PEF Invoice No. 61887899 Ref. CPS
17PEF Invoice No. 61890392 Ref. CPS

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:244px;"></colgroup><tbody>
</tbody>



End result will be:-

A
1Description
2ice No. 61724556 Ref. CPS
3ice No. 61738222 Ref. CPS
4ice No. 61798350 Ref. CPS
5ice No. 61804695 Ref. CPS
6ice No. 61882858 Ref. CPS
7ice No. 61781054 Ref. CPS
8ice No. 65047248 Ref. CPS
9ice No. 67007403 Ref. CPS
10ice No. 61909416 Ref. CPS
11ice No. 61873772 Ref. CPS
12ice No. 61748818 Ref. CPS
13ice No. 61763096 Ref. CPS
14ice No. 61760588 Ref. CPS
15ice No. 61886842 Ref. CPS
16ice No. 61887899 Ref. CPS
17ice No. 61890392 Ref. CPS

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:244px;"></colgroup><tbody>
</tbody>




Thanking you in advance.





 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Based on your table try (your table has the sample data and the result both in column A but you state in your text B2 :confused:)

Code:
Sub ddd()
    Dim cRng As Range
    Application.ScreenUpdating = False
    For Each cRng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        cRng.Value = Right(cRng.Value, 25)
    Next
    Application.ScreenUpdating = True
End Sub
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,868
Office Version
  1. 2010
Platform
  1. Windows
Noting for the record that you say your data is in Column B even though your picture shows it in Column A.

It is not clear from your description if you want the results to replace the original data or if you want the results in the next column over. If you want the original data overwritten, then give this code a try...
Code:
Sub Last25Characters()
  Dim Addr As String
  Addr = "B2:B" & Cells(Rows.Count, "B").End(xlUp).Row
  Range(Addr) = Evaluate("RIGHT(" & Addr & ",25)")
End Sub
If you wanted the output in Column C instead, just change the last line of code to this...
Code:
  Range(Addr)[B][COLOR="#FF0000"].Offset(, 1)[/COLOR][/B] = Evaluate("RIGHT(" & Addr & ",25)")
 

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
61
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Based on your table try (your table has the sample data and the result both in column A but you state in your text B2 :confused:)

Code:
Sub ddd()
    Dim cRng As Range
    Application.ScreenUpdating = False
    For Each cRng In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        cRng.Value = Right(cRng.Value, 25)
    Next
    Application.ScreenUpdating = True
End Sub


Works great, Thanks.
Appreciate your prompt reply, thanking you once again.:)
 

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
61
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Noting for the record that you say your data is in Column B even though your picture shows it in Column A.

It is not clear from your description if you want the results to replace the original data or if you want the results in the next column over. If you want the original data overwritten, then give this code a try...
Code:
Sub Last25Characters()
  Dim Addr As String
  Addr = "B2:B" & Cells(Rows.Count, "B").End(xlUp).Row
  Range(Addr) = Evaluate("RIGHT(" & Addr & ",25)")
End Sub


Rick Rothstein; Many thanks for your solution, but unfortunately it's not working, all data in selected column (B) clears, i tried it several times in different sheet.

By the way, i want to know the difference in both techniques, the one you used and the one provided by MARK858.
In MARK858 technique, the code is run in a loop (individual action on each record), takes extra time (in my opinion) for huge data sheets.
where as in your technique it's instant for the whole column.

I am sorry if i am not able to explain my query.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try this copied to a standard module.

Assumes you have column E free to use as a helper column, but is cleared as a final result.

Howard

Code:
Option Explicit

Sub aSUBSTITUTE_Col()
  Dim lRowCount&
 
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row
  
  With Range("E2").Resize(lRowCount)
    .Formula = "=SUBSTITUTE(A2, ""PEF Invoice"", ""ice"")": .Value = .Value
  End With
  
  With Range("E2").Resize(lRowCount)
    .Copy Range("A2")
    .ClearContents
  End With
   
End Sub
 

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
61
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Dear L. Howard,
Thank you for your solution, but what i understood from your code is that it's splinting the cell from "ice". My data consists of mix characters, this was just a sample data.
Anyhow, thank you for your care & help.



@Rick Rothstein
What i understood so far is your code is based on string, so what's MARK858 Technique?

Thanks, just trying to understand more about vba.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Rick Rothstein; Many thanks for your solution, but unfortunately it's not working, all data in selected column (B) clears, i tried it several times in different sheet.

By the way, i want to know the difference in both techniques, the one you used and the one provided by MARK858.
In MARK858 technique, the code is run in a loop (individual action on each record), takes extra time (in my opinion.) for huge data sheets.
where as in your technique it's instant for the whole column.
QUOTE]

First of all Rick's code works fine for me with the sample you provided, if your data is in column B so you need to post what is different in your actual data .

Second your assessment of the difference between the 2 codes is correct (and it is not an opinion, it is a fact that Rick's will run faster, especially over large data sets).

What i understood so far is your code is based on string, so what's MARK858 Technique?

The same it just removes any characters more than 25 from the right and puts the result back in the cell.
 
Last edited:

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
61
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Dear MARK858,
After trying again, i found out the reason why it wasn't working with me. My first record B2 was empty, which resulted in no data for the rest of the records.
Any idea how i can adjust code to make it work even if the first few records are nil? I also tried with first few cells less than 25 characters, after running code, it results in same no. of characters as the first one, eg., if first cell (B2) value is "No.61724556 Ref. CPS", all the rest records have exact same value.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:21px;" /><col style="width:144px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >Description</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">5</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">11</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">12</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">13</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">14</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">15</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">16</td><td >No. 61724556 Ref. CPS</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">17</td><td >No. 61724556 Ref. CPS</td></tr></table>

Thanks.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Are you talking about the code I posted, Rick's or both ? and can you post a sample of the data before the code is run with all the situations you currently have shown please.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,518
Messages
5,625,271
Members
416,086
Latest member
CaptainGD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top