فصل الارقام عن الحروف فى اكسيل بدون معادلات

مهارة  تقسيم الإسماء دفعة واحدة   فى اكسيس

===============================

اولا معرفة بعض الدوال فى اكسيس خاصة بالتعامل مع الحروف 

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

LTrim, RTrim, and Trim functions

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

إرجاع متغير (سلسلة) يحتوي على نسخة من سلسلة محددة دون مسافات بادئة (LTrim) أو مسافات زائدة (RTrim) أو مسافات بادئة وزائدة (Trim).

Dim MyString, TrimString MyString = " <-Trim-> " ' Initialize string.TrimString = LTrim(MyString) ' TrimString = "<-Trim-> ".TrimString = RTrim(MyString) ' TrimString = " <-Trim->".TrimString = LTrim(RTrim(MyString)) ' TrimString = "<-Trim->".' Using the Trim function alone achieves the same result.TrimString = Trim(MyString) ' TrimString = "<-Trim->".

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Len function

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Type CustomerRecord ' Define user-defined type. ID As Integer ' Place this definition in a Name As String * 10 ' standard module. Address As String * 30End Type Dim Customer As CustomerRecord ' Declare variables.Dim MyInt As Integer, MyCur As Currency Dim MyString, MyLen MyString = "Hello World" ' Initialize variable.MyLen = Len(MyInt) ' Returns 2.MyLen = Len(Customer) ' Returns 42.MyLen = Len(MyString) ' Returns 11.MyLen = Len(MyCur) ' Returns 8.

============
The second example uses LenB and a user-defined function (LenMbcs) to return the number of byte characters in a string if ANSI is used to represent the string.

Function LenMbcs (ByVal str as String) LenMbcs = LenB(StrConv(str, vbFromUnicode)) End Function Dim MyString, MyLen MyString = "ABc"' Where "A" and "B" are DBCS and "c" is SBCS.MyLen = Len(MyString) ' Returns 3 - 3 characters in the string.MyLen = LenB(MyString) ' Returns 6 - 6 bytes used for Unicode.MyLen = LenMbcs(MyString) ' Returns 5 - 5 bytes used for ANSI.


\

\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Mid function

\\\\\\\\\\\

Returns a Variant (String) containing a specified number of characters from a string.

Dim MyString, FirstWord, LastWord, MidWords MyString = "Mid Function Demo" ' Create text string.FirstWord = Mid(MyString, 1, 3) ' Returns "Mid".LastWord = Mid(MyString, 14, 4) ' Returns "Demo".MidWords = Mid(MyString, 5) ' Returns "Function Demo".

مهارات فى اكسيس

مسلسل	اسم الملف	لينك	المدة ب الدقيقة
1	معرفة عدد الاحرف او الكلمات الموجودة في احد الحقول في اكسيس Dcount	https://youtu.be/WE0_of3cU0g	12
2	كيفية اخفاء شاشة الاكسس واظهار شاشة البرنامج مباشرة	https://youtu.be/ePZKCngCXhI	3
3	برنامج أدخل تاريخ ميلادك يعطيك عمرك بالسنة و الشهر و اليوم MS Access Skills	https://youtu.be/kuocx5KbRbk	5
4	مهارة تنسيق التاريخ فى اكسيس من خلال الدالة و format	https://youtu.be/ma1CJv2YFZQ	10
5	مهارة تقسيم الإسماء دفعة واحدة فى اكسيس MS Access VBA	https://youtu.be/mBUP70LnOdk	10
1000	قائمة ملفات الكورس الاول -- تعلم اكسيس وقواعد بيانات MS Access	https://www.youtube.com/playlist?list=PLMmy9Ec9B98wxI2RWVrjjtyV2knTFoR1t	0
2000	Programming Access VBA قائمة ملفات الكورس الثانى-- البرمجة فى اكسيس	https://www.youtube.com/playlist?list=PLMmy9Ec9B98ymtXXOLw2FeolA904Z-OsH	0

دالة  ازالة   المسافات  التى ليس لها لازمة  من  الاسم او الجملة او الفقرة 

بطريقتان

اليك الكود

===================

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Private Sub Command13_Click()
MsgBox NoSpaces1([txtName])
End Sub
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Function NoSpaces1(InName As String) As String
Dim NewName  As String, NeXStr As String
Dim I As Integer
NewName = ""
InName = Trim(InName)
For I = 1 To Len(InName) - 1
    If Mid(InName, I, 1) = " " And Mid(InName, I + 1, 1) = " " Then NewName = NewName Else NewName = NewName & Mid(InName, I, 1)
    NeXStr = Mid(InName, I + 1, 1)
  Next

NoSpaces1 = NewName + NeXStr


End Function
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Function NoSpaces(InName As String) As String
Dim NewName As String, ThePrevStr As String, TheStr As String
Dim I As Integer

InName = Trim(InName)
 MsgBox "InName" & InName & Len(InName)
For I = 1 To Len(InName)
    TheStr = Mid(InName, I, 1)
    If TheStr = " " And ThePrevStr = " " Then TheStr = ""
    If TheStr <> "" Then ThePrevStr = TheStr
    NewName = NewName & TheStr
    MsgBox I & "TheStr" & TheStr & "-" & Chr(13) & "ThePrevStr" & ThePrevStr & "-" & Chr(13) & "NewName" & NewName & "."

Next

NoSpaces = NewName


End Function
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


<

InStr function

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Example

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Dim SearchString, SearchChar, MyPos SearchString ="XXpXXpXXPXXP" ' String to search in.SearchChar = "P" ' Search for "P". ' A textual comparison starting at position 4. Returns 6.MyPos = Instr(4, SearchString, SearchChar, 1) ' A binary comparison starting at position 1. Returns 9.MyPos = Instr(1, SearchString, SearchChar, 0) ' Comparison is binary by default (last argument is omitted).MyPos = Instr(SearchString, SearchChar) ' Returns 9. MyPos = Instr(1, SearchString, "W") ' Returns 0.

\\\\\\\\\\\\\\\\\\\\